Level 24
What exactly isn't working?
Jun 3, 2019
7
Level 1
Conditional INSERT
Hi there,
I am struggling to convert this conditional SQL into Builder code. Any help is much appreciated....
Given these inputs ['id' => 1, 'bid_id' => 2, 'bid' => 3] i want to insert if either there is not bid yet or the given bid is higher than any previous.
I've tried the SQL as-is, but cannot get it into Eloquent (at least not without making it just one big string which I'd like to avoid).
SQL:
INSERT INTO bids (id, bid_id, bid)
// given id, bid_id and bid
SELECT 1, 2, 3
WHERE
NOT EXISTS (SELECT 1 from bids where bid_id = 2)
OR
NOT EXISTS (SELECT 1 from bids where bid_id = 2 HAVING max(bid) >= 3)
$id = 1;
$bidId = 2;
$bid = 3;
$select = static::query()
->whereNotExists(function (Builder $query) use ($bidId) {
$query
->from('bids')
->selectRaw(DB::raw(1))
->where(['bid_id' => $bidId]);
})
->orWhereNotExists(function(Builder $query) use ($bidId, $bid) {
$query
->from('bids')
->selectRaw(DB::raw(1))
->where(['bid_id' => $bidId])
->havingRaw('max(bid) >= ?', [$bid]);
})
->select(DB::raw(sprintf('%d, %d, %d', $id, $bidId, $bid)));
$model = DB::table('auction_bids')->insertUsing(['user_id', 'auction_id', 'bid'], $select);
Level 1
As it is it does not insert anything at all.
Level 1
Also, I am curious if there is a way around using the DB::raw (sprintf(..)) construct and use proper bindings instead. I seem to have issues using named bindings for some reason. (OTOH, I do not want to mix different issues...)
Level 24
You can use selectRaw():
->selectRaw('?, ?, ?', [$id, $bidId, $bid]);
Does the table actually contain data that matches your constraints?
What's the result of dd($select->get());?
Level 1
Yeah, selectRaw()... I've tried so many things, loosing track :)
I managed to track down the actual SQL executed which looks like this:
insert into `bids` (`id`, `bid_id`, `bid`)
select 1, 1, 12 from `auction_bids`
where
not exists (select 1 from `auction_bids` where (`bid_id` = 1))
or
not exists (select 1 from `auction_bids` where (`bid_id` = 1) having max(bid) >= 12)
The issue is the from `auction_bids` in select 1, 1, 12 from `auction_bids`. When I remove that and execute the SQL manually it works.
Kinda makes sense as I am not really trying to select from the table directly....
So, the question now is how to supress the from `auction_bids` on the select
Level 24
You can remove the FROM clause like this:
$select = static::query()->from(null)
Level 1
Perfect! Thanks a lot!
Please or to participate in this conversation.