Merging two search queries in a single dataProvider?

Yii 2.0 General Discussions
1

Hi all.

I have this issue in a Yii2 project and would appreciate some help.

I’ve got a table Article, it has a relation with table Stock so some articles are in stock and some don’t.

The customer wants to get sorted searches, but he needs the articles with stock to show first.

So all Articles with stock should appear first in the datagrid, sorted by description (or whatever order the user sorts them). Then, in the same datagrid, all Articles without any available stock would show next, again sorted.

Is there any way to build a dataProvider to feed the datagrid from both lists?

2

Hello mefumo
you can use union , to merge 2 queries
https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#union
The first query show the articles with stock and the other the same but without stock.
After that you pass directly the merge query to dataProvider
You dont have to worry about the duplicates , union do the work for you and remove duplicates

1 Like
3

I think what you want is leftJoin() Article table with Stock table in one query and feed the results to your datagrid.
Then you DESC sort on the column in Stock table, that represents, if an article is present.

1 Like
4

jmar1988 solution looks like what I am looking for. Thanks.

Androphin, wouldn’t that leave me with no results for Articles with stock null or plainly not present? The original database I need to work with is the usual desktop application disasterpiece with no data consistency as it has been randomly pactched as needs urged along the time. Happy days.

Thanks again!

5

@MeFuMo If you have an Articel record, that has no stock record, the return value in stock column with left join will be NULL, but the article is still in your query results.
Inner join wouldn’t have such article records in the query result.
I maybe misinterpreted your:

I’ve got a table Article, it has a relation with table Stock so some articles are in stock and some don’t.
So your relation guarantees always, that your article_id is in stock table? Then inner join.
Don’t see why it’s nessecary to merge two queries with an union.

Something like:
SELECT * FROM article INNER JOIN stock ON article.id=stock.article_id ORDER BY stock.amount DESC, article.description ASC;

6

@Androphin thanks for the answer.

You are right, LEFT JOIN will give me the NULL results.

This project is killing me, sorry. Also the title of the post is plainly wrong.

The UNION thing does not seem to work properly as I don’t really want to merge tables. I will try to explain myself better, sorry but English is not my first language. I should:

  • Retrieve all the results from Articles with a stock, sorted by description.
  • Retrieve all the results from Articles without any stock, again sorted by description.
  • Paste one ordered list (the one without stock) below the other and send it as a Search result or dataprovider thing.

So when the user makes a search, the results will display first the items with stock, sorted by description, then after them the ones without stock, again sorted by description.

I think I need two queries to get that result in that order.

I am finding to have two queries a bit confusing in the search model. When I load the $params I am just getting one of the queries filtered.

I am not sure how to approach this, so any help would be appreciatted.

7

if you are using ActiveRecord then you obviously have a hasMany relation in Article model referencing the Stocks.

create another function in Article that counts how many Stocks an Article has:

public function getStocksCount(){
     return $this->getStocks()->count();
}

in your datapovider:

$provider = new \yii\data\ArrayDataProvider([
        'allModels' => Article::find()->all(),
        'sort' => [
            'attributes' => ['stocksCount', 'description'],
            'defaultOrder' => [
                'stocksCount' => SORT_DESC,
                'description' => SORT_ASC, 
            ]
        ],
        'pagination' => [
            'pageSize' => 10,
        ],
    ]);

This will sort the ones with stocks at the top and those without will follow (Descending order)

8

Sorry if it may be a bit late for an answer :stuck_out_tongue:

I found an extension that did exactly what I needed, It plainly takes two DataProvider with their own configuration and pastes one after another.

I link it here as it may be useful to someone. From here, thanks to the creator, as he saved me time I didn’t had: