n:m query problem

[FlipFlop]

Hello guys!

I have a "maybe" simple problem which caused me to stay awake for some nights... Here is the problem:

I have 3 tables:

MEMBERS (contains many members who can do more than one sport)
SPORTS (contains many sports e.g. BMX, Skateboard,...)
MEM2SPORTS (contains the linking between member_id and sports_id)

The result of this tables should be rendered by DataGrid to a table that should look like:

[mark]

I might overlook something, but can't you use joins in a single query to fetch the data from your three tables?

The limiting feature would then still be used, and even counting should work as expected as long as you don't need to group records (if you need to, you can use the 'count_query' option).

I can provide another idea if joins don't solve your problem, but that idea would require more work for you.

[alex]

[mark]

[FlipFlop]

Hi there,

Alex got me right. With an INNER JOIJN query I get as result for each sport per member a seperate record. So if a member was added to 2 sports mysql resturns 2 records for this member. For example such a result looks like:

[alex]

[FlipFlop]

Will wait for mark too, but maybe you have also a good way to what I have some questions:

How does DataGrid know that member 1 has more than one sport and further more put an additional column to the table?

And how can I use implode() in your sense? Once the query is passed to bind() I can't make anything with it except looking at my poor table

ps: If necessary I can post the whole sql dump of my tables so you can define your idea of a query better (I own up to be not a sql guru)

Will have a look at the formatter function in the meantime.

[mark]

Okay, I now got the idea (an example is always good, BTW). Grouping would be an option, like Alex suggested. But that would generate multiple rows for the different sports -- possible, but maybe not the wanted way. (Doing some magic with formatter functions might help here.)

Another possiblity would be to write your own DataSource driver. This gives you the full flexibility over the data and the columns. Whenever a new sport occurs while fetching the data, a new column could be added. I'd suggest, though, that you get a list of all sports at first, to make a concise number of columns through all pages, and not three on page 1, seven on page 2, four on page 3 etc.

This might sound complex. It's not as easy as simply binding a SQL query, but it is possible. A good start might be two start the work from the MDB2 driver (from CVS because we heavily refactored the handling of SQL queries since the last releases).

If needed, I can provide more help / details here.

[FlipFlop]

Hi mark!

Sounds great and smells of a lot of work
I think the solution with the data source driver would be the best way, as you wrote it gives more flexibility. And regarding the sport columns: Yes the will and must have a concise number of columns through all pages. If e.g. 5 sports exists and a member was only added to one sport the rest 4 columns are displayed as well with value "No".

Since analyzing either foreign or old self-code is time-consuming it would be very appreciated if you can provide more details of how to rewrite the MDB2 driver for my special needs.

Many thanks in advance!
Mike

[mark]