addSelect with exists

Hi everyone,

I have 2 models :

card: a simple model
cardlist: a custom list created by a user

The relation is many-to-many.

i'm trying to get all cardlist of the user, and check if every cardlist has a specified card. To do that, I want to use a subqurey, but it's not working via Eloquent.

Here is my code:

$query = Cardlist::addSelect(['has_card' => function($q) use ($card) {
        $q->select('id')
            ->from('cards_in_cardlists')
            ->where('card_id', $card)
            ->whereColumn('list_id', 'cardlists.id')
            ->limit(1)
            ->exists()
        ;
    }])
    ->get()
;

And my error:

Column not found: 1054 Unknown column 'cardlists.id' in 'where clause' (SQL: select exists(select * from cards_in_cardlists where card_id = 19 and list_id = cardlists.id limit 1) as exists)

This query works perfectly:

select `cardlists`.*, (
	select exists(
		select `id` 
		from `cards_in_cardlists` 
		where `card_id` = 19 
		and `list_id` = `cardlists`.`id` 
		limit 1
	) as `exists`
) as `has_card` 
from `cardlists`

So maybe I make a mistake using Eloquent, but I don't know where.

Can someone help me? Thank you :)

bobbybouwmann

5 years ago

Level 88

We can probably make this query work, but why not use the relationship to only fetch the items you really need here?

// This grabs all cardlists where this card exists.
$cardllists = Cardlist::whereHas('card', function ($query) use ($card) {
    return $query->where('id', $card);
})->get();

If you still need a list of all cardlists and only want to add the column we need to make your query work of course. I don't see anything strange on your query, cardlists.id should be available here.

MichalOravec

5 years ago

Level 75

@bobbybouwmann whereHas instead of has

$cardllists = Cardlist::whereHas('card', function ($query) use ($card) {
    $query->where('id', $card);
})->get();

1 like

bobbybouwmann

5 years ago

Level 88

@michaloravec whereHas is an alias for has

public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
{
    return $this->has($relation, $operator, $count, 'and', $callback);
}

Please don't just copy my answer and change one word...

MichalOravec

5 years ago

Level 75

@bobbybouwmann Yes it is just alias but

public function has($relation, $operator = '>=', $count = 1, $boolean = 'and', Closure $callback = null)

In your answer as second parameter of has should be operator and not closure

$cardllists = Cardlist::has('card', function ($query) use ($card) {
    return $query->where('id', $card);
})->get();

And I don't want to copy you, just remind you, that you made a mistake.

bobbybouwmann

5 years ago

Level 88

@michaloravec Thanks! I updated my answer

1 like

DimZ

5 years ago

Level 4

What I want to do is to propose to the user to add in his list the card he is viewing. To do this, I want to show him all his cardlists, indicating the cardlists where it is already added or not.

To be clearer, the user is on a card. He clicks on "add to my cardlist" button, it opens a modal with all his cardlists, with a checkbox next to each. He can check/uncheck each list to add/remove the card to the cardlist. So I need to make my query work :)

Currently I make 2 queries + merge results in my controller to do this job, but this is frustrating when your query is working but impossible to do with Eloquent. I have this feeling every time I have to make a query, It's getting on my nerves.

Thank you for your help :)

Please or to participate in this conversation.