Обсуждение: Inject rows into query resultes
Hi,
I am having a problem where I need to inject rows into a query result. That is
I have a list of add records, lets say car listings which I select from a table. The order of the results are given either by the user or default/initial.
I now want to inject featured car adds after every 5th record. (coming from the same table)
Right now, I am doing this on the PHP just calling the query twice but I am wondering if there is a way to only run one query.
The main problem is that
a) i am using paging so the original listing should not be affected
b) the injected records should not be in the results returned for the current page.
Any one having an idea how to solve that?
Thanks
Alex
On Tue, Sep 22, 2015 at 6:22 PM, Alex Magnum <magnum11200@gmail.com> wrote:
Hi,I am having a problem where I need to inject rows into a query result. That isI have a list of add records, lets say car listings which I select from a table. The order of the results are given either by the user or default/initial.I now want to inject featured car adds after every 5th record. (coming from the same table)Right now, I am doing this on the PHP just calling the query twice but I am wondering if there is a way to only run one query.
I have no clue how you expect to "run one query" AND "so the original listing should not be affected".
The main problem is thata) i am using paging so the original listing should not be affected
b) the injected records should not be in the results returned for the current page.
I have no clue what these mean since if you don't intend to show them why retrieve the feature car ads (one d) in the first place?
Any one having an idea how to solve that?
Solving the ordering problem has a simplistic brute-force algorithm (you may need to fix off-by-one errors in the logic - but the idea holds)
SELECT *, (((row_number-1) / 5) * 10) + ((row_number-1)) % 5) AS "rows_0_to_4_in_the_current_decade"
UNION ALL
SELECT *, ((row_number - 1) * 10) + (5) AS "row_5_in_the_current_decade"
Now you have: [0-5] [10-15] [20-25], etc... as your "row index" where every "5" record is your injected row.
Hopefully this helps - you have provided insufficient and/or contradictory information regarding your actual problem.
David J.