1

I have table like this:

id   |   user   |   parent_id   |   level   |
---------------------------------------------
1    |  parent1 |       0       |     1
2    |  parent2 |       0       |     1
3    |  parent3 |       1       |     2
4    |  child1  |       1       |     2
5    |  child2  |       4       |     3

From child2 I would like to check if it belongs to parent1.

An obvious answer would be run a query from each level starting from child2 > check parent > check parent > until it is parent1. But that will be a lot of query to run. Like:

while ($parent = \DB::table('users')->where('parent_id', $child->parent_id)->first()) {
    if ($checkparent->id == $parent->id) break; // found the checked parent
    else $child = $parent;
}

Is there any way to run this with just one query? (Note: it will be more than 2 levels)

parent1  <-- to here            parent2
/      \
parent3  child1
           \
          child2 <-- from here
Strawberry
  • 33,750
  • 13
  • 40
  • 57
user2002495
  • 2,126
  • 8
  • 31
  • 61
  • http://dba.stackexchange.com/questions/46393/get-top-most-parent-by-nth-child-id Not doable with mysql. Tbh, it is a microoptimisation. Since you are already using laravel, few more requests to the database shouldn't be a big concern. Assuming `id` is a primary key, the queries should be quick. – Alex Blex Jan 27 '17 at 09:00
  • @AlexBlex: how about 500 levels? – user2002495 Jan 27 '17 at 09:03
  • Then reconsider data model or storage engine. Mysql doesn't fit for purpose. – Alex Blex Jan 27 '17 at 09:04
  • To elaborate, you can add `top_parent` field to the model, or use e.g. Postgres: http://stackoverflow.com/questions/14659856/postgresql-query-for-getting-n-level-parent-child-relation-stored-in-a-single-ta – Alex Blex Jan 27 '17 at 09:08
  • Just to make it crystal clear, any SQL solution will do the '500' queries, even if it is coded in stored procedure/udf and looks like a 'single' query from laravel side. If you deal with really big trees, you'd better employ something designed for it, e.g. neo4j or any other graph db. – Alex Blex Jan 27 '17 at 09:16
  • Consider revising your data model – Strawberry Jan 27 '17 at 09:48

1 Answers1

-1

I think you are looking for this:

Nested has statements may also be constructed using "dot" notation. For example, you may retrieve all posts that have at least one comment and vote:

// Retrieve all posts that have at least one comment with votes...
$posts = Post::has('comments.votes')->get();

For more info check here - https://laravel.com/docs/5.3/eloquent-relationships

You need to make sure that the relationship is configured properly in the models.

Anton Grigorov
  • 139
  • 5