I'm doing a project where the database was designed before me and they
don't have a budget for a database re-design. There are two entity
tables which /should/ have been the same model with some meta-fields
keyed off a type field, but it's not. It's two tables, which should be
displayed intermixed, paged, and sorted by date.

The solution I thought up didn't do the trick. I:
- made two pagers, one for each table
- got the current page's set
- intermixed them by making an array of references, keyed off the
datetime string, refering to items in both result sets
- krsort()ed the intermixed array

The result is probably obvious, but I'll explain it anyway... there is
nowhere near an equal density in entries-per-date between the two
tables, and so, each page shows results from both tables, but the dates
for the two models on the same page are very different; on page 1, i'll
get items from tableA with dates ranging 10/5/2007-10/9/2007 and tableB
with dates ranging from 10/13/2007 12:00:00 to 10/13/2007 at 15:00:00.

So I'm thinking I need to find a way to align the results, and it needs
to happen somehow in the pagers themselves (this being symfony, the
pager grabs the query criteria object and sets limit and offset for
you.) I am already generating pager links based on the resultset with
the most pages; so artificially generating page links won't be a far
step.

The only solution I've thought up, I don't want to do, which is to break
the query into dates e.g. if the date range is 2007-10-01 to 2007-10-31,
I do not want a separate pager link for each date which then subpages
the results or shows all results for that date; the date range might
span years and there might be hundreds of entries for a date. I need a
single pager which aligns the two tables' entries' dates in each page of
results throughout the span.

I am very open to a ready-made class that can do this, especially if
it's based on Propel, or if it can take raw sql queries.

Thoughts?

Nathan

Nevermind. Just because they can't afford a database redesign doesn't
mean they can't afford a new index table containing date-indexed links
to both tables entities.


On Sat, 2007-10-20 at 21:39 -0400, Nathan Hawks wrote:
> I'm doing a project where the database was designed before me and they
> don't have a budget for a database re-design. There are two entity
> tables which /should/ have been the same model with some meta-fields
> keyed off a type field, but it's not. It's two tables, which should be
> displayed intermixed, paged, and sorted by date.

damn hotmail blows....

But if the database supports it, what about using a view that is made up ofthe two tables?

Could that be a possibility?

Bastien






----------------------------------------> From: nhawks@gmail.com> To: php-general@lists.php.net> Date: Sat, 20 Oct 2007 21:39:03 -0400> Subject: [php]Multi-table pager sorted by date>> I'm doing a project where the database was designed before me and they> don't have a budget for a database re-design. There are two entity> tables which /should/ have been the same model with some meta-fields> keyed off a type field, but it's not. It's two tables,which should be> displayed intermixed, paged, and sorted by date.>> The solution I thought up didn't do the trick. I:> - made two pagers, one for each table> - got the current page's set> - intermixed them by making an arrayof references, keyed off the> datetime string, refering to items in both result sets> - krsort()ed the intermixed array>> The result is probably obvious, but I'll explain it anyway... there is> nowhere near an equal density in entries-per-date between the two> tables, and so, each page shows results from both tables, but the dates> for the two models on the same page are very different; on page 1, i'll> get items from tableA with dates ranging 10/5/2007-10/9/2007 and tableB> with dates ranging from 10/13/2007 12:00:00 to 10/13/2007 at 15:00:00.>> So I'm thinking I need to find a way to align the results, and it needs> to happen somehow in the pagers themselves (thisbeing symfony, the> pager grabs the query criteria object and sets limit and offset for> you.) I am already generating pager links based on the resultset with> the most pages; so artificially generating page links won't be afar> step.>> The only solution I've thought up, I don't want to do, which is to break> the query into dates e.g. if the date range is 2007-10-01 to 2007-10-31,> I do not want a separate pager link for each date which then subpages> the results or shows all results for that date; the date range might> span years and there might be hundreds of entries for a date. I need a> single pager which aligns the two tables' entries' dates in each page of> results throughout the span.>> I am very open to a ready-made class that cando this, especially if> it's based on Propel, or if it can take raw sql queries.>> Thoughts?>> Nathan>>>> --> PHP General Mailing List (http://www.php.net/)> To unsubscribe, visit: http://www.php.net/unsub.php>

__________________________________________________ _______________
Express yourself with free Messenger emoticons. Get them today!
http://www.freemessengeremoticons.ca/?icid=EMENCA122

Possibly... I've never read about MySQL views, so I can't refute it

The problem at hand is solved, but thanks. Might have to give views a
glance in TFM.