Using field data in an ON clause

Databases
1

MySQL: Using field data in an ON clause

I’m trying to do a variation on Bill Karwin’s “ClosureTable” using a self-join instead of an extra table. For this I’m putting the ids I want to find in an extra field in the main table:

CREATE TABLE IF NOT EXISTS `test` (
  `id` smallint(6) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `father_id` smallint(6) NOT NULL default '0',
  `mother_id` smallint(6) NOT NULL default '0',
  `father_ids` text NOT NULL,
  `mother_ids` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

The query is as follows:

SELECT p.id, p.name, f.father_ids, f.mother_ids
FROM test as f
CROSS JOIN test AS p ON p.id IN (f.mother_ids)
WHERE f.id = '$id'

If the father_ids or mother_ids field has more than one value it only matches the first one. How do I convert the field value into a proper operand for the IN clause?

2

Make a normalized data model instead. The first rule of normalization is to never store multiple values in a column.

3

CROSS JOIN with an ON condition like yours just doesn’t make sense to me (even though mysql allows it), i think it should be INNER JOIN instead

you could use the FIND_IN_SET function for your unnormalized data, but be aware the performance sucks and the query won’t scale up

4

Normalize, yes. Thank you. :slight_smile: But at this point I’m exploring the various ways to traverse a family tree and if “breaking the rules” improve the code, I don’t mind doing it. The area where I’m having issues is in displaying all ancestors and all descendants. These are essentially linked lists which one normally handles with loops or recursive functions which I’m seeking to avoid. :rolleyes:

5

Yes! I pasted my experimental test code as is (sorry). :smiley:

You could use the FIND_IN_SET function for your unnormalized data, but be aware the performance sucks and the query won’t scale up

Thanks, I’ll give it a try. :slight_smile:

6

in that case, look up celko’s nested set model

look ma, no loops or recursion

:cool:

7

It works like a charm! :wink:

Since FIND_IN_SET() returns the position of the found element, as long as my data is in the right order it even provides the proper sort order.

Even if “performance sucks,” won’t getting rid of loops and recursion make up for it?

BTW, what do you mean “won’t scale up?” Are you referring to the 64 element limit is sets? I’m using a text field for my “set”! :cool:

in that case, look up celko’s nested set model

That’s one avenue I have not yet explored. While researching this project one poster said it was awfully complicated which turned me off. But I should take a look at it even if I now have what looks like a working solution. I still have to check how difficult is to build and maintain my “sets.”

8

my remark about performance and not scaling applies to the unnormalized data with FIND_IN_SET

the nested set model is ridiculously fast for selects

the poster who said it was complicated was likely me (if your search was here on sitepoint)

i personally don’t like the backwards handsprings you need to do with the nested set model for inserts and deletes

9

It was on stackOveflow: http://stackoverflow.com/a/5215988/1116878

Fairly simple single call solution that uses an adjacency list implementation with a non recursive stored procedure. Would recommend avoiding nested sets like the plague - best left in the classroom those !

BTW, my solution seems to be full of holes :eek: :mad: :nono: