MySQL Syntax error SQL Error [1064] [42000]: You have an error in your SQL syntax

Question

So I have the following code:

public function scopePopular($query, $limit = 10)
    {
        $query->leftJoin(DB::raw('(SELECT COUNT(id) visits, UNIQUE(ip_address) FROM `visitors`) visitors'), function($join)
        {
            $join->on('visitors.visitorable_id', '=', 'db885a18-f0b7-4f55-9d93-743fbb5d9c94');
            $join->where('visitors.visitorable_type', '=', 'App\Profile');
        });

        return $query;
    }

which produces the following query:

select * from `profiles` inner join `profile_genres` on `profiles`.`id` = `profile_genres`.`profile_id` left join (SELECT COUNT(id) visits, UNIQUE(ip_address) FROM `visitors`) visitors on `visitors`.`visitorable_id` = `db885a18-f0b7-4f55-9d93-743fbb5d9c94` and `visitors`.`visitorable_type` = ? where `profile_genres`.`genre_id` = ? and `profiles`.`deleted_at` is null

This query doesn't appear to work, I'm guessing due to a syntax error and MYSQL isn't exactly specific in its error messages.

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE(ip_address) FROM visitors)on visitors.visitorable_id = 'db885a18-f0' at line 1

score 1 · Accepted Answer · answered Aug 26 '17 at 11:23

1

As far as I know there is no such construct like UNIQUE(ip_address) for SELECT clause. You probably want:

SELECT COUNT(id) visits, ip_address
FROM `visitors`
GROUP BY ip_address

answered Aug 26 '17 at 11:23

Lukasz Szozda

162,964
23
234
275

Returns an error: No database selected. – ChrisBratherton Aug 26 '17 at 11:32
@ChrisBratherton well, select one :) – Shadow Aug 26 '17 at 11:36
Sorry, seemed to be running part of the query. So This has got me a little closer, however, Laravel is now throwing another hissy fit... – ChrisBratherton Aug 26 '17 at 11:45
Column not found: 1054 Unknown column 'my uuid' in 'on clause' (SQL: select `profiles`.*, `profile_genres`.`genre_id` as `pivot_genre_id`, `profile_genres`.`profile_id` as `pivot_profile_id` from `profiles` inner join `profile_genres` on `profiles`.`id` = `profile_genres`.`profile_id` left join (SELECT COUNT(id) visits, ip_address, visitorable_id, visitorable_type FROM `visitors` GROUP BY ip_address) visitors on `visitors`.`visitorable_id` = `my uuid` and `visitors`.`visitorable_type` = App\Profile where `profile_genres`.`genre_id` = myuuid and `profiles`.`deleted_at` is null) – ChrisBratherton Aug 26 '17 at 11:45
@ChrisBratherton Sorry but I don't know from where you get `visitors.visitorable_id = my uuid` On your samples there is no such column/value/parameter. – Lukasz Szozda Aug 26 '17 at 11:48
1

@ChrisBratherton this seems to be a different question than you asked. If you have a follow up question, then pls ask it separately in itws own question, not just in a comment. – Shadow Aug 26 '17 at 11:56

MySQL Syntax error SQL Error [1064] [42000]: You have an error in your SQL syntax

1 Answers1