[FIXED] How can I convert int to string in laravel eloquent?

Issue

My mysql query is like this :

SELECT b.id, b.name, COUNT(*) AS count_store
FROM stores a
JOIN locations b ON CONVERT(b.id,CHAR) = a.address->'$."regency_id"'
GROUP BY b.id
ORDER BY count_store DESC
LIMIT 10 

If mysql query I use this : CONVERT(b.id,CHAR) to convert int to string

How can I convert int to string in laravel eloquent? I'm using Laravel 5.3.

I had try like this :

$stores = Store::select('locations.name','COUNT(*) AS count_store')
               ->join('locations', 'locations.id', '=', 'stores.address->regency_id')
               ->groupBy('locations.id')
               ->orderBy('count_store', 'desc')
               ->limit(10)
               ->get();

But it does not work

Solution

If you want to use a very custom join condition, such as one involving a cast of one or both columns, then I'm not sure this would be possible without at least some kind of raw syntax. Hence, you should consider the answer given by @Rodrane as well as this one:

$stores = Store::select('locations.id', 'locations.name', DB::raw('COUNT(*) AS count_store'))
               ->join('locations', DB::raw("CONVERT(locations.id, CHAR)"), '=', 'stores.address->regency_id')
               ->groupBy('locations.id', 'locations.name')
               ->orderBy('count_store', 'desc')
               ->limit(10)
               ->get();

Note that I made the following changes:

• Used DB::raw() to give a custom alias to the count
• Added location.name to the GROUP BY (and select) clause
• Used another DB::raw() to handle the cast in the join condition

Answered By - Tim Biegeleisen

Answer Checked By - Marie Seifert (PHPFixing Admin)