Query Builder Triggers 404 Status on Working SQL Query

Question

Query Builder Triggers 404 Status on Working SQL Query

JellyM Aug '16

Created Aug '16	Last Reply Aug '16	Replies 2	Views 396	Votes 0

JellyM 2.3k

Aug '16

I don't know what is the problem but following working SQL query doesn't work with PhalconPHP;

SELECT p.id AS id, p.image AS image, p.title AS title, p.status AS status, c.name AS category, c.id AS category_id FROM posts AS p INNER JOIN categories AS c ON c.id = p.category_id WHERE p.deleted = :deleted AND (p.title LIKE :search_0 OR c.name LIKE :search_1) ORDER BY id DESC LIMIT :APL0 OFFSET :APL1

Here is the rest of the data

["bind"]=>
array(4) {
  ["deleted"]=>
  int(0)
  ["search_0"]=>
  string(7) "%fast %"
  ["APL0"]=>
  int(10)
  ["APL1"]=>
  int(0)
}
["bindTypes"]=>
array(4) {
  ["deleted"]=>
  int(1)
  ["search_0"]=>
  int(2)
  ["APL0"]=>
  int(1)
  ["APL1"]=>
  int(1)
}

I got this information from Phalcon's query builder ->getQuery()->getSQL();

When I replace placeholders with their respected values and run the query, it works as expected. Unfortunately when I run ->getQuery()->execute(); I'm getting 404 error.

Could anyone tell me where am I doing wrong please?

Nikolay Mihaylov · Answer 1 · 2016-08-22T22:56:31-07:00

Nikolay Mihaylov
93.7k

Aug '16

Can you show us your Query Builder code (php) ?

JellyM · Answer 2 · 2016-08-23T12:02:44-07:00

It would be really complicated as I have whole classes managing this. When I don't use joins, it works as expected though.

JellyM · Answer 3 · 2016-08-24T12:11:52-07:00

Alright now the weird thing is if I remove p.title and p.statuscolumns it works, if I add them it doesn't. If I add these columns and remove AND (p.title LIKE :search_0 OR c.name LIKE :search_1) from WHERE statement, it works again... This is very confusing.

It seems it just doesn't like some conditions and columns depending on the whole query...

PMA doesn't have any problems to execute the query though.

Nikolay Mihaylov · Answer 4 · 2016-08-24T23:29:50-07:00

My guess is that your "Class" managing your query builder is messing things up. I've ran almost identical query like yours against a test table of mine and it is working as intended.

$data = $this->modelsManager->createBuilder()
    ->columns([
        'main.id',
        'main18.title'
    ])
    ->from(['main' => 'Models\News'])
    ->leftJoin('Models\NewsI18n', 'main18.foreign_key = main.id', 'main18')
    ->where('main.is_active = 1')
    ->andWhere('(main18.lang LIKE :lang: OR main.category_id LIKE :category:)', ['lang' => 'bg', 'category' => '1'])
    ->orderBy('main.id DESC')
    ->limit(3)
    ->getQuery()->execute();

Output of print_r($data->toArray());:

Array
(
    [0] => Array
        (
            [id] => 7
            [title] => Dumfries Chihuahua attack witnesses sought
        )

    [1] => Array
        (
            [id] => 7
            [title] => Dumfries Chihuahua attack witnesses sought
        )

    [2] => Array
        (
            [id] => 6
            [title] => 'Rude' bear statue removed near China securities HQ
        )

)