S
S
Sharov Dmitry2015-06-19 12:22:03
MySQL
Sharov Dmitry, 2015-06-19 12:22:03

Database error in query with JOIN Laravel v5?

I make a selection from the database (here is only part of the code, during which an error occurs)

if(count($rgColl) > 0){
                $this->projects->join('object_collection', function($join) use($rgColl){
                    $join->on('object_collection.object_id', '=', 'objects.id');
                    if(count($rgColl) == 1){
                        $join->where('object_collection.collection_id', '=', $rgColl[0]);
                    } else {
                        $join->whereIn('object_collection.collection_id', $rgColl);
                    }
                });
                $this->select[] = 'object_collection.object_id';
                $this->select[] = 'object_collection.collection_id';
                $this->group[]  = 'object_collection.object_id';
            }

If there is 1 element in $rgColl, then everything is OK, the result is returned normally
if the $join->whereIn('object_collection.collection_id', $rgColl);
gives an error message
SQLSTATE[42000]: Syntax error or access violation: 1064 check the manual that corresponds to your MySQL server version for the right syntax to use near '? where `objects`.`category_id` = ? group by `object_parameters_size`.`object_id' at line 1

I take the query code generated by Lara, and just execute it in MySQL Workbench, everything is cool, the data is returned.
The complete query looks like this
SELECT 
    `objects`.`id`,
    `objects`.`ord`,
    `objects`.`title`,
    `objects`.`art`,
    `objects`.`code`,
    `object_parameters_size`.`object_id`,
    `object_parameters_size`.`parameter_id`,
    `object_parameters_size`.`valint`,
    `object_collection`.`object_id`,
    `object_collection`.`collection_id`
FROM 
  `objects`
  INNER JOIN `object_parameters` AS `object_parameters_size` ON `object_parameters_size`.`object_id` = `objects`.`id`
    AND `object_parameters_size`.`parameter_id` = 1
    AND `object_parameters_size`.`valint` > 0
    AND `object_parameters_size`.`valint` < 500
  INNER JOIN `object_collection` ON `object_collection`.`object_id` = `objects`.`id`
        AND `object_collection`.`collection_id` IN (38,15,39,40,41)
WHERE
    `objects`.`category_id` = 1
GROUP BY `object_parameters_size`.`object_id` , `object_collection`.`object_id`
ORDER BY `objects`.`ord` ASC

There is some kind of anomaly.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
S
Sharov Dmitry, 2015-07-13
@vlom88

Figured out what the problem was. Essence in the following
In the Illuminate\Database\Query\JoinClause class there was no whereIn method, as for the main body of the request. I added it, but it's a little wrong.
Initially, the line
It looks like this
AND when checking the syntax in via PDO in the select method of the Illuminate\Database\Connection class
We are getting an error. In general, there is no anomaly here, but entirely my blunt

A
Alexey Karagodnikov, 2015-06-19
@VenZell

Print the query using the toSql() method and double-check if your code column is escaped with back quotes. This is a reserved keyword. Most likely this is the problem.
The error clearly indicates that the request uses a reserved keyword.
List of new keywords in MySQL 5+

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question