I am executing one query that will return me 8 results. It is very complex, so I am not going to post it here, because maybe it doesn't matter, maybe what I am trying to do is not possible, but if it is, I hope someone can guide me. So first query returns 8 results, and I will grab the ID of each result row. I am doing this because in second query I need to use those Ids in WHERE NOT IN statement. I mean, second query will return 18 results, because 8 will be ignored since they was found with first query.
Let me explain further. I had one query that was returning me 26 results. But customer wanted to order those result by criteria that is not available if I do not execute second query and put results of that query on top of results list. I mean, instead of having one query and one result list, now I have two queries but their results should be combined into one list where results from the first query go on top. But there comes the problem with pagination.
Before I execute first query using Yii2 DAO, I am creating pagination object that has totalCount equal to 26 ( I have one count query that will return me how many results I have in total ). But my first query have 8 results, second got 18. If I limit page results display to 10, pagination will not work.
Here is some pseudo code that may help:
// search query that will return number of ids found by certain criteria
$count = $model->countOffers($condition);
// build pagination object
$pagination = new Pagination(['totalCount' => $count, 'pageSize' => 10]);
// execute first query that will return the array of results
// inside this query $pagination object is used to build LIMIT and OFFSET
$firstQuery = $model->getFirst($condition, $pagination);
// get ID's that should be ignored in next query
foreach ($firstQuery as $data)
{
$ignoreIds[] = $data['id'];
}
// execute second query that will return results without $ignoredIds being used
// inside this query $pagination object is used to build LIMIT and OFFSET
// $secondQuery = $model->getSecond($condition, $pagination, $ignoredIds);
// render view and display pagination there, but pager is broken
$this->render('index', ['firstQuery' => $firstQuery, 'secondQuery' => $secondQuery, 'pagination' => $pagination]);
I am not giving you concrete code, because I am not sure if I am trying to do possible or impossible thing. So I would like if someone can give me a clue if this is possible to do, and if yes, what would be the steps to achieve it.
EDIT: this is the index view:
<?php if ($queryOne): ?>
<?= $this->render('_index', ['data' => $queryOne]) ?>
<?php endif ?>
<?php if ($queryTwo): ?>
<?= $this->render('_index', ['data' => $queryTwo]) ?>
<?php endif ?>
Instead to split into 2 different results, mark missing articles as "missing" using your complex query in a left join
select if(missing_id is null, 1,0) missing, ...
from articles as a
left join (select id as missing_id ... your-complex-query)
as miss on a.id = miss.missing_id
So you will have a single result of 26 lines, 8 with missing to 1 and the others with missing to 0.