how to pass a pure sql query to laravel eloquent-CodePudding

This SQL:

SELECT COUNT(*) AS total_see_all_video
from users u
WHERE 7 = (SELECT COUNT(*) FROM lecciones_users lu WHERE lu.uuid = u.uuid)

I tried this code but did not work:

$data = LeccionesUsers::select(
    DB::raw('COUNT(*) AS total_ase_vis_videos'),
    DB::raw('where 7 = (SELECT COUNT(*) FROM lecciones_users where leccion_users.uuid = users.uuid)')
)
    ->join('users', 'lecciones_users.uuid', '=', 'users.uuid')
    ->get();

CodePudding user response：

Your issue is that you are not correctly forming your query. You can do ->toSql(); instead of ->get(); and you would see the final SQL (would definitely not be the same as the one you wrote first).

So, you should have this to have the same SQL:

$total_see_all_video = LeccionesUsers::whereRaw('7 = (SELECT COUNT(*) FROM lecciones_users where leccion_users.uuid = users.uuid)')
    ->count();

Please, try my query (and also run ->toSql() to see if you have a correct SQL).

I would still recommend to use relationships and it is very weird to do 7 = query.

CodePudding user response：

You can convert this to laravel query builder or can break this query to parts to be used accordingly. You can use this section for the reference purpose: Laravel-Raw-Expressions

Hope this will help you with the result.