Help with query for parent and child

@silencebringer

that's not what I'm looking for it returns... probably as one would expect.

id	467
user_id	1
discussion_id	70
parent_id	467
body	"and here"
created_at	"2021-08-03T03:07:26.000000Z"
updated_at	"2021-08-03T03:07:26.000000Z"

I want both records...

0	
id	467
user_id	37
discussion_id	70
parent_id	null
body	"Quidem cupiditate voluptas ut dolor assumenda. Eos rerum dolore qui dicta tenetur vitae repudiandae. Dolor voluptates atque delectus iure. Ut impedit facere et sed. Quia repellat et aliquid blanditiis eos. Qui laborum nam aliquam minus quis. Accusamus necessitatibus vero officia optio et."
created_at	"2021-08-03T03:06:50.000000Z"
updated_at	"2021-08-03T03:06:50.000000Z"

1	
id	468
user_id	1
discussion_id	70
parent_id	467
body	"the search term"
created_at	"2021-08-03T03:07:26.000000Z"
updated_at	"2021-08-03T03:07:26.000000Z"

@silencebringer

oh, that's close... it returns...

	
0	
id	468
user_id	1
discussion_id	70
parent_id	467
body	"and here"
created_at	"2021-08-03T03:07:26.000000Z"
updated_at	"2021-08-03T03:07:26.000000Z"

1	
id	null
user_id	null
discussion_id	null
parent_id	null
body	null
created_at	null
updated_at	null

@webrobert possible just need to specify columns to select

Reply::select('replies.*')
	->leftJoin('replies as child', 'replies.id', 'child.parent_id')
	->where(fn ($query) => $query->where('replies.body', 'like', '%' . 'the search term' . '%')
		->orWhere('child.body', 'like', '%' . 'the search term' . '%'))
	->get();

@davidifranco

oh, that's pretty elegant. Map sort of became my go-to for this kind of thing and I overlooked transform. In asking this question I was thinking doing it in the query maybe was a cheaper way to get the results?

Here I just added flatten()...

Reply::where('body', 'like', '%' . 'the search term' . '%')
                ->with('Parent')->get()
                ->transform( fn($reply) => ['parent' => $reply->parent, 'reply' => $reply])
                ->flatten();