Sort records based on dragSort of another table

4 posts by 3 authors in: Forums > CMS Builder
Last Post: April 20, 2011   (RSS)

By rjbathgate - April 20, 2011

Hi,

This might be tricky.

Table "tbl_courses" includes field "Discipline"

This Field "Discipline" is linked to table "tbl_disciplines", with 'num' recorded in the mySQL in tbl_courses.

So user creates new Course record, and selects the appropriate Discipline.


When listing the COURSES, and sorting by DISCIPLINE, it sorts then by NUM of the Discipline records.

However, how could we set the sort to sort by DISCIPLINE based on the dragSortOrder of the records in "tbl_discipline", rather than NUM?

I can only forsee a long winded messy hack type way of doing it... (which might only work in my head, not on paper) is there any efficient simple way?!

Thanks
Rob

Re: [gkornbluth] Sort records based on dragSort of another table

By rjbathgate - April 20, 2011

Hey,

Thanks for the thread Jerry.

Unless I'm missing something however it doesn't cover sorting one table of records based on the order of linked records in a second table, which is the issue I'm having.

Cheers
Rob

Re: [rjbathgate] Sort records based on dragSort of another table

By Dave - April 20, 2011

Hi Rob,

There's an efficient way, and a simple way, but not both.

The simple way is to do lots of queries. Just load Discipline, sort it by dragSort and output it, then in the foreach load courses that match that discipline. So you get many many getRecords() calls, but if your database isn't too big you can probably get away with it.

The efficient way is to write a pure MySQL query with a JOIN and sort on that, but that will take some experimenting.

You can do a lot with the viewer functions but things like this are still often easier done by hand.

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com