Anyone here familiar with DB_DataObject (If you don’t know it, a general answer would still be very helpful)? I’m trying to figure out how to handle a n:n (or 1:n in some cases) relationship.
I have 3 tables:
Filter
+-----------+ | Field | +-----------+ | filter_id | | name | | type | +-----------+
Feed
+---------------+ | Field | +---------------+ | id | | title | | htmlUrl | | description | | categorise | | etag | | last_modified | | status | +---------------+
And feed_filter
+-----------+ | Field | +-----------+ | feed_id | | filter_id | +-----------+
The tasks I need to perform:
* Get all filters attached to a feed
* Get all feeds attached to a filter
* Get all filters attached to all feeds (so when printing a list of feeds I can show the filters)
What is the proper way to handle these tasks? Do I have to do 2 queries for each of these, or is there a better way?
7 comments ↓
Looks like something that you could do in one query if you have the ability to do subselects. If this is on MySQL, subselects are only supported in the development versions (4.1 and 5), not the production version, 4.0.x. Many other databases support subselects though, like PostgreSQL.
Without subselects I believe that you would have to do two queries to get the results you want.
No subselects are needed (if I understand your tasks correctly).
//all filters and feeds
SELECT feed.*, filter.* FROM feed
INNER JOIN feed_filter ON feed.feed_id=feed_filter.feed_id
INNER JOIN filter ON filter.filter_id=feed_filter.filter_id
// all filters from feed X
SELECT * FROM filter
INNER JOIN feed_filter ON filter.filter_id=feed_filter.filter_id
WHERE feed_id=X
// all feed from filter Y
SELECT * FROM feed
INNER JOIN feed_filter ON feed.feed_id=feed_filter.feed_id
WHERE filter_id=Y
BTW I could’t post a comment in opera, because the username and password where not shown (the dialogbox was to small). I almost give up, but then I tried IE and IE has a bigger dialogbox, so I could read it.
Thanks for your post Remy! I have not managed to get the first query working, but the other two do. Will the first query work for 0:n as well (as that is what my local database is currently).
re the Opera problem, this is known as I use Opera as my main browser. I have reported this bug to them a couple of times but no fix yet - can you pester them as well? It can’t be that hard to deal with
Will this work?
SELECT feed.*, filter.* FROM feed
INNER JOIN feed_filter ON feed.feed_id=feed_filter.feed_id
INNER JOIN filter ON feed_filter.filter_id=filter.filter_id
What do you mean with an 0:n relation? Do you mean that you have feeds without filters? You call that an 0:n relation? If so then they first query will not do that.
You should change the query to (not tested):
SELECT feed.*, filter.* FROM feed
LEFT JOIN feed_filter ON feed.feed_id=feed_filter.feed_id
INNER JOIN filter ON feed_filter.filter_id=filter.filter_id
bq. Will this work?
Yes thanks, that works!
bq. What do you mean with an 0:n relation? Do you mean that you have feeds without filters? You call that an 0:n relation?
I don’t know what this should be called, but I do have feeds without filters. Maybe the easiest work-around is to assign all feeds added to a filter that does nothing, but I’d prefer not to have to do this
bq. You should change the query to (not tested):
That one didn’t work for 0:n queries (or whatever they’re called) - it returned the same results as the first query.
For 1:N linking see
http://pear.php.net/manual/en/package.database.db-dataobject.intro-relations.php
Concentrate to “links”, there are also table joins discussed but I never really needed them.
For N:N linking
I implemented such stuff in FormBuilder. See the source in the CVS at
http://cvs.php.net/co.php/pear/DB_DataObject_FormBuilder/FormBuilder.php?r=1.47
search for “crossLinks” and “tripleLinks”. crossLinks is for linking 2 tables with a crosslink table between them (a typical M:N relationship in an RDBMS). This is exactly what you specified above. tripleLinks is for joining 3 tables with a crosslink table, this is a bit more exciting
simple exampe scenario for crosslinks: storing dvd subtitle languages (one dvd can have arbitrary number of languages as subtitles):
dvds
—-
id
title
description
etc..
languages
–
id
name
subtitles
—-
dvd_id
language_id
With this stuff you can store things like:
dvd #1 - english
dvd #1 - german
dvd #2 - english
etc.
simple exampe scenario for triplelinks (storing dvds and audio data):
dvds
—-
id
title
description
etc..
languages
–
id
name
audio
–
id
name
dvd_audiodata (links the 3 tables above)
–
dvd_id
lang_id
audio_id
With this stuff you can store things like:
dvd #1 (dvds) - english (languages) - dolby digital (audio)
dvd #1 (dvds) - english (languages) - dolby surround (audio)
dvd #1 (dvds) - english (languages) - dolby stereo (audio)
dvd #1 (dvds) - german (languages) - dolby digital (audio)
dvd #1 (dvds) - german (languages) - dolby stereo (audio)
Well.. and FormBuilder handles all this perfectly .. at least the CVS version.
You better wait for the first stable release of DB_DataObject_FormBuilder.
..or..
Even if you don’t plan to use FormBuilder, you can get the idea from there.
Regards,
Norbert Mocsnik
–
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder
Hi,
it’s working correct with custom Entity created by the user ,but if u make the relation between the Custom entity & CRM User entity it cann’t create the relationship attribute in the User Table in database & it cann’t working properly
Leave a Comment