creating mysql query using Yii2 Query Builder, strpos: array given

Question

I'm trying to make query builder to output the following MySQL query:

SELECT p1.id, p1.product_id, p1.updated_at
FROM tbl_scrape_data p1
INNER JOIN
(
   SELECT max(updated_at) MaxDate, product_id
   FROM tbl_scrape_data
   WHERE product_id IN (1,2,3)       
   GROUP BY product_id
) p2
ON p1.product_id = p2.product_id
AND p1.updated_at = p2.MaxDate
WHERE p1.product_id IN (1,2,3)   
order by p1.updated_at desc

Here's what I tried:

$scrapeData = (new Query() )
        ->select(['p1.product_id', 'p1.id', 'p1.updated_at'])
        ->from('tbl_scrape_data p1')
        ->innerJoin([
            'p2' => (new Query)
                ->select(['MAX(updated_at) MaxDate', 'product_id' ])
                ->from('tbl_scrape_data')
                ->where([ 'product_id' => [1, 2, 3, 15, 4] ])
                ->groupBy('product_id'),
                //->all(),
            ['p1.product_id' => 'p2.product_id', 'p1.updated_at' => 'p2.MaxDate']
            ])
        ->where([ 'p1.product_id' => [1, 2, 3, 15, 4] ])
        ->orderBy('p1.updated_at DESC')
        ->all();

Yii2 throws an error trying to perform this query. Can someone tell me if this is a Yii2 bug or if I'm missing something? Or maybe the way I formatted the query builder is wrong?

I'm using Yii 2.0.1 and the error is

strpos() expects parameter 1 to be string, array given

\vendor\yiisoft\yii2\db\QueryBuilder.php at line 715

Are you sure the error is in that code?I dont see any strpos() — Mihai
– Mihai, Commented Jan 11, 2015 at 22:50
Yes, please not that I'm using Yii 2.0.1. The function that throws the error is called quoteTableNames in class QueryBuilder. The line looks like this "} elseif (strpos($table, '(') === false) {" — Alex Sutu
– Alex Sutu, Commented Jan 11, 2015 at 23:05

arogachev · Accepted Answer · 2015-01-12 04:23:19Z

3

The reason of the error is in this section of code:

->innerJoin([
    'p2' => (new Query)
        ->select(['MAX(updated_at) MaxDate', 'product_id' ])
        ->from('tbl_scrape_data')
        ->where([ 'product_id' => [1, 2, 3, 15, 4] ])
        ->groupBy('product_id'),
        //->all(),
        ['p1.product_id' => 'p2.product_id', 'p1.updated_at' => 'p2.MaxDate']
])

Instead of passing two parameters - table and on separately you actually passing them in one parameter - array.

Also the first parameter (table) should be array with one element.

In other words, the placement of square brackets is incorrect.

So here is correct code of the INNER JOIN section:

->innerJoin(
    ['p2' => (new Query)
        ->select(['MAX(updated_at) MaxDate', 'product_id' ])
        ->from('tbl_scrape_data')
        ->where([ 'product_id' => [1, 2, 3, 15, 4] ])
        ->groupBy('product_id'),
        //->all(),
    ],
    ['p1.product_id' => 'p2.product_id', 'p1.updated_at' => 'p2.MaxDate']
)

Check the official documentation for innerJoin() method.

edited Jan 12, 2015 at 4:23

answered Jan 12, 2015 at 4:18

arogachev

33.6k8 gold badges119 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Sutu Over a year ago

it worked, I had to adjust the ON parameter in order to make it look exactly as my query but the main problem was how I formatted the innerjoin parameters. Thanks @arogachev

Collectives™ on Stack Overflow

creating mysql query using Yii2 Query Builder, strpos: array given

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related