CODEWITHSUNDEEP
laraveleloquent
user11352561
user11352561

Reputation: 2637

Laravel - Syntax error or access violation in Aggregate Function

I want to perform aggregate function and achieve the result shown in the diagrame below:

summary

New Result:

incorrect result

I have this query:

        $subscribers= Cloudsubscriptions::join("services","cloudsubscriptions.service_name","=","services.name")
            ->join("service_type","services.service_type","=","service_type.id")
            ->select("cloudsubscriptions.service_name"
                    ,"service_type.name as service_type"
                    ,DB::raw("(SELECT count(cloudsubscriptions.id) FROM 'cloudsubscriptions' WHERE 'is_subscribe' = 1) as total_active_users")
                    ,DB::raw("(SELECT count(cloudsubscriptions.id) FROM 'cloudsubscriptions' WHERE 'is_subscribe' = 0) as total_inactive_users")
                    ,DB::raw("(SELECT count(cloudsubscriptions.id) FROM 'cloudsubscriptions') as total_users"))
            ->groupBy("cloudsubscriptions.service_name")
        ->get();

I expect to have a result show in the diagram, but I got this error

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''cloudsubscriptions' WHERE 'is_subscribe' = 1) as total_active_users, (SELECT co' at line 1 (SQL: select cloudsubscriptions.service_name, service_type.name as service_type, (SELECT count(cloudsubscriptions.id) FROM 'cloudsubscriptions' WHERE 'is_subscribe' = 1) as total_active_users, (SELECT count(cloudsubscriptions.id) FROM 'cloudsubscriptions' WHERE 'is_subscribe' = 0) as total_inactive_users, (SELECT count(cloudsubscriptions.id) FROM 'cloudsubscriptions') as total_users from cloudsubscriptions inner join services on cloudsubscriptions.service_name = services.name inner join service_type on services.service_type = service_type.id group by cloudsubscriptions.service_name order by service_name asc limit 15 offset 0) ◀"

How do I resolve it please?

Upvotes: 0

Views: 297

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

The error is caused by the single quotes around table and column names in the raw expressions.

Remove them or use backticks:

$subscribers= Cloudsubscriptions::join("services","cloudsubscriptions.service_name","=","services.name")
    ->join("service_type","services.service_type","=","service_type.id")
    ->select("cloudsubscriptions.service_name"
            ,"service_type.name as service_type"
            ,DB::raw("(SELECT count(cloudsubscriptions.id) FROM `cloudsubscriptions` WHERE `is_subscribe` = 1) as total_active_users")
            ,DB::raw("(SELECT count(cloudsubscriptions.id) FROM `cloudsubscriptions` WHERE `is_subscribe` = 0) as total_inactive_users")
            ,DB::raw("(SELECT count(cloudsubscriptions.id) FROM `cloudsubscriptions`) as total_users"))
    ->groupBy("cloudsubscriptions.service_name")                ^                  ^
->get();

Upvotes: 1

Related Questions