1

I have a small problem with a php mysql query, I am looking for help.

I have a family tree table, where I am storing for each person his/her ancestors id separated by a comma. like so

 id ancestors
 10 1,3,4,5

So the person of id 10 is fathered by id 5 who is fathered by id 4 who is fathered by 3 etc...

Now I wish to select all the people who have id x in their ancestors, so the query will be something like: select * from people where ancestors like '%x%'

Now this would work fine except, if id x is lets say 2, and a record has an ancestor id 32, this like query will retrieve 32 because 32 contains 2. And if I use '%,x,%' (include commas) the query will ignore the records whose ancestor x is on either edge(left or right) of the column. It will also ignore the records whose x is the only ancestor since no commas are present.

So in short, I need a like query that looks up an expression that either is surrounded by commas or not surrounded by anything. Or a query that gets the regular expression provided that no numbers are around. And I need it as efficient as possible (I suck at writing regular expressions)

Thank you.

Edit: Okay guys, help me come up with a better schema.

Joe Yahchouchi
  • 627
  • 7
  • 16
  • You have come up with a bad database design. You will never get this to work consistantly or be easily queryable. You need to stop and do some reading on how to create one to many relationships using a relational database. Just do a google and bathe in the resultant knowledge – RiggsFolly Mar 08 '15 at 16:37
  • 2
    You should read this: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Parag Tyagi Mar 08 '15 at 16:42
  • I know about one to many. And usually I would store for each record his immediate parent record. However if I wish to get all the children and grandchildren for a given record using one query, how would I do it? I don't want to retrieve the children, then do separate query for their children, I thought this way I would do one query and get them all. – Joe Yahchouchi Mar 08 '15 at 16:43
  • Actually, materialized path gets quite good press in some circles! – Strawberry Mar 08 '15 at 16:43

7 Answers7

2

You are not storing your data in a proper way. Anyway, if you still want to use this schema you should use FIND_IN_SET instead of LIKE to avoid undesired results. 

SELECT *
  FROM mytable
 WHERE FIND_IN_SET(2, ancestors) <> 0
Jordi Llull
  • 810
  • 6
  • 17
1

You should consider redesigning your database structure. Add new table "ancestors" to database with columns:

id id_person ancestor
1  10        1
2  10        3
3  10        4

After -- use JOIN query with "WHERE IN" to choose right rows.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
drozdo
  • 319
  • 2
  • 11
1

You're having this issue because of wrong design of database.First DBMS based db's aren't meant for this kind of data,graph based db's are more likely to fit for this kind of solution.

if it contain small amount of data you could use mysql but still the design is still wrong,if you only care about their 'father' then just add a column to person (or what ever you call it) table. if its null - has no father/unknown otherwise - contains (int) of his parent.

In case you need more then just 'father' relationship you could use a pivot table to contain two persons relationship but thats not a simple task to do.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Gal Sisso
  • 1,939
  • 19
  • 20
  • I don't think it's accurate to say DBMS's aren't meant for this type of data. Other solutions may do it more efficiently/effectively, but a properly designed DBMS should be able to handle a large number of hierarchical records. – mesch Mar 08 '15 at 19:03
  • If its less efficient then its not meant to do that.. I dont know whats the purpose of this family tree but if you will try to build multiple/big family tree you will end up waiting at least couple of seconds to fetch the tree – Gal Sisso Mar 08 '15 at 19:12
1

There are a few established ways of storing hierarchical data in RDBMS. I've found this slideshow to be very helpful in the past:

Models for Hierarchical Design

Since the data deals with ancestry - and therefore you wouldn't expect it to change that often - a closure table could fit the bill.

Whatever model you choose, be sure to look around and see if someone else has already implemented it.

mesch
  • 195
  • 9
0

You could store your values as a JSON Array 

id | ancestors
10 | {"1","3","4","5"}

and then query as follows:

$query = 'select * from people where ancestors like \'%"x"%\'';

Better is of course using a mapping table for your many-to-many relation

Edwin Krause
  • 1,766
  • 1
  • 16
  • 33
0

You can do this with regexp:

SELECT * FROM mytable WHERE name REGEXP ',?(x),?'

where x is your searched value

keversc
  • 346
  • 1
  • 3
  • 9
0
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,ancestors VARCHAR(250) NOT NULL
);

INSERT INTO my_table VALUES(10,',1,3,4,5');

SELECT *
  FROM my_table
 WHERE CONCAT(ancestors,',') LIKE '%,5,%';
+----+-----------+
| id | ancestors |
+----+-----------+
| 10 | ,1,3,4,5  |
+----+-----------+

SELECT *
  FROM my_table
 WHERE CONCAT(ancestors,',') LIKE '%,4,%';
+----+-----------+
| id | ancestors |
+----+-----------+
| 10 | ,1,3,4,5  |
+----+-----------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57