Laravel: Query works directly on MySQL but not on DB

Question

I have the following query:

SELECT cn.id, cn.title, cn.type, cn.status FROM (
    SELECT v.concert_id as concert_id, SUM(v.position_votes + v.support_votes) as votes
    FROM (
        SELECT q.concert_id as concert_id, q.position_id as position_id, q.position_votes as position_votes, SUM(q.votes_up + q.votes_down) as support_votes
        FROM ( 
            SELECT qcs.id AS concert_id, p.id AS position_id, p.votes AS position_votes, IF(s.votes_in_favor <=> null, 0, s.votes_in_favor) AS votes_up, IF(s.votes_not_in_favor <=> null, 0, s.votes_not_in_favor) AS votes_down     
            FROM (
                SELECT concert_id AS id 
                FROM positions p 
                WHERE p.content LIKE '%%' 
                GROUP BY concert_id
            ) 
            AS qcs 
            JOIN positions p
            ON p.concert_id = qcs.id
            LEFT JOIN supports s
            ON s.position_id = p.id
        ) AS q
        GROUP BY q.position_id
    ) as v
    GROUP BY v.concert_id
) as r
JOIN concerts cn on cn.id = r.concert_id
ORDER BY r.votes DESC, cn.created_at DESC

When I made that query directly into MySQL I get the desired results. But when I put that query using DB, for example:

$query = "...alll_the_previous_query";
$result = DB::select(DB::raw($query));

I got the following error:

local.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1055 'q.concert_id' isn't in GROUP BY ...the rest of the query

I know that a way to avoid this is to change the database configuration in Laravel and change the strict to false. But that is not an option.

What is wrong with my query when I passed it to Laravel?

Laravel is using strict mode per default. You should fix your query according to the error message. — Paul Spiegel, Apr 22 '19 at 20:55
Yes but I've tried a lot and I don't know exactly what the error is. — Jacobo, Apr 22 '19 at 20:57
And when you directly run the query, then mysql does not use the strict mode. Configure mysql to use the strict sql mode and you will get the error message when you directly run the query as well. — Shadow, Apr 22 '19 at 20:58
@JacoboTapia mysql error message in this particular case tells you exactly what the error is. However, it may be easy to fix this error message, but you will get a different one after that. We do not know enough about your task to know how to fix the query. — Shadow, Apr 22 '19 at 21:04
What is the MySQL version? Is `positions.id` PRIMARY KEY? Is `SUM(q.votes_up + q.votes_down) as support_votes` correct or do you rather mean `q.votes_up - q.votes_down`? — Paul Spiegel, Apr 22 '19 at 21:55
Yes, all the ids are primary keys. Also the sum is correct, because we are trying to get the concert with most interaction, and that is measured by all the votes, positives and negatives. — Jacobo, Apr 22 '19 at 22:29

score 0 · Answer 1 · answered Apr 22 '19 at 20:58

0

You can add the q.concert_id to the group by. Instead of:

GROUP BY q.position_id

Use:

GROUP BY q.concert_id, q.position_id

answered Apr 22 '19 at 20:58

Thiago Barcala

6,463
2
20
23

This may make the error message go away, but the OP will get another error message. If you fix that one, then the query will probably run, but will produce different results than the current version without the fix mode. – Shadow Apr 22 '19 at 21:02
Actually It does not, It worked well. I have to add more elements to the group by, but the result were exactly the same. – Jacobo Apr 22 '19 at 21:05
@JacoboTapia if you had to add more elements to the group by, then this solution did not work well... – Shadow Apr 22 '19 at 21:07

Laravel: Query works directly on MySQL but not on DB

1 Answers1