Level 102
Do you mean something like distinct()?
https://laravel.com/docs/8.x/queries#specifying-a-select-clause
It just ensures there are no duplicates in your query
Nov 20, 2021
7
Level 7
How to write query to exclude specific matching records
I have this mock table rich_guys. Notice there are records with the same slug 1|6 & 3|5. Also 2 slugs have a foo_id 3|6.
+----+------------------------------+------------+
| id | slug | foo_id |
+----+------------------------------+------------+
| 1 | bill-gates | NULL |
| 2 | jeff-bezos | NULL |
| 3 | steve-jobs | 23 |
| 4 | elon-musk | NULL |
| 5 | steve-jobs | NULL |
| 6 | bill-gates | 64 |
| 7 | mark-zuckerberg | NULL |
+----+------------------------------+------------+
How would I write a query so records 1 and 5 are removed and the rest remain?
Simulated output:
+----+------------------------------+------------+
| id | slug | foo_id |
+----+------------------------------+------------+
| 2 | jeff-bezos | NULL |
| 3 | steve-jobs | 23 |
| 4 | elon-musk | NULL |
| 6 | bill-gates | 64 |
| 7 | mark-zuckerberg | NULL |
+----+------------------------------+------------+
Talking it out, I'm thinking I'd need to do something like this, but not sure how to write the query.
get all richGuys
if guy has foo_id and matching slug
show guy with foo_id
if guy has no foo_id and no matching slug
show guy
return richGuys
I'm looking for advice, or a tutorial with this kind of edge case scenario that would help me complete this task. Although I would not frown upon a helpful solution. =)
Level 7
@Sinnbeck That might help, but in this case I think I would still need a distinct() with some kind of where or if clause. Right?
Level 102
@daugaard47 can you show the query?
Level 7
@Sinnbeck That's my problem lol. I'm not sure how I would go about writing it. I'm looking into SubQueries now. I think that might help me out. I'll show some code once I figure this out more.
Level 73
@daugaard47 Are you sure this is just one query, because it's sounds like more than one.
- get all richGuys
- if guy has foo_id and matching slug
- show guy with foo_id
- if guy has no foo_id and no matching slug
- show guy
Queries
-
RichGuy::all() -
RichGuy::whereNotNull('foo_id, ')->where('slug', $request->slug)->get() -
RichGuy::where('foo_id', $request->foo_id)->get() -
RichGuy::whereNull('foo_id')->where('slug', '<>', $request->slug)->get() -
RichGuy::findOrFail($request->id)
Level 7
@Tray2 I guess that's where I'm stuck. I using the same table so I thought I'd be able to do it in one query, but I was exploring using 2 queries and then merging them together.
Something like this:
$richGuysDefault = RichGuy::where('foo_id', null); // Get all records
$richGuysSpecific = RichGuy::where('foo_id', $this->foo->foo_id); // Gets the records where there is a foo_id
$richGuys = $richGuysDefault->merge($richGuysSpecific); // Merge is not working here
$this->foo->foo_id is coming from another table that's being passed in to the page.
Then if I had the tables merged I could probably use a couple If statements to output the results I need in the view. Not sure how to merge that correctly though, also wasn't sure if that was the best way. Hints why I'm here asking for advice.
Level 73
@daugaard47 I see.
Something like this then?
SELECT *
FROM rich_guys
WHERE (foo_id IS NOT NULL AND slug IN ('bill-gates', 'steve-jobs'))
OR (foo_id IS NULL AND slug NOT IN ('bill-gates', 'steve-jobs'))
ORDER BY id;
Please or to participate in this conversation.