Custom Query To eloquent

Hello, i am new in laravel, and i don't know how to convert a custom query to eloquent

i have a payment cards system, and want to display a summary about activated and deactivated cards, with prices and counts

my query is :

SELECT *,
       activatedCardsPrice + deactivatedCardsPrice as totalPrice
FROM (
         SELECT *,
                SUM(cardValue * deactivatedCardsCount) AS deactivatedCardsPrice,
                SUM(cardValue * activatedCardsCount)   AS activatedCardsPrice
         FROM (
                  SELECT 
                         u.username,
                         u.address,
                         u.phone_number,
                         c.cardValue,
                         SUM(CASE WHEN activated = 0 THEN 1 ELSE 0 END) AS deactivatedCardsCount,
                         SUM(CASE WHEN activated = 1 THEN 1 ELSE 0 END) AS activatedCardsCount
                  from cards c
                           LEFT JOIN users u on c.user_id = u.id
                  group by user_id, cardValue)
                  as s
         group by id
     )
         as s1;

every table in query is a model.

thanks ..

jlrdw

6 years ago

Level 75

Something like that I'd use as is see:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Or use an eloquent query, generally follows a format like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

https://laravel.com/docs/5.8/queries

But you could also set up eloquent relations if you want to:

https://laravel.com/docs/5.8/eloquent

https://laravel.com/docs/5.8/eloquent-relationships

I'd suggest viewing some videos on some of these various topics.

And or actually work the examples Taylor provides in the documentation.

willvincent

6 years ago

Level 54

Sometimes it really is easier to roll with a raw query.

Not everything needs to be run through eloquent ;)

engHusseinMansour

6 years ago

Level 1

My goal is to use paginate() funtion, so i do not do it manually !

jlrdw

6 years ago

Level 75

Usually in a group by I don't paginate, but you can:

https://laracasts.com/discuss/channels/guides/manual-pagination-episode-3

Just that easy. Also:

https://laracasts.com/discuss/channels/guides/length-aware-paginator

From docs:

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

He does not mean write your own, he means length aware paginator, just requires some basic calculations.

Please or to participate in this conversation.