0

First question ever on stack, I really couldn't find the answer elsewhere

I am making a ticket reservation system and I would like one query to create a ticketsorder if the total of ordered tickets after this call would be equal or smaller than the maxtickets value of tickettype and the maxticketstype of the event.

I have 3 tables

  1. events with columns id, maxtickets
  2. tickettypes with columns id, event_id, maxtickets
  3. ticketsorders with columns id, tickettype_id, amount (amount is number of tickets)

The reason I want to do it in 1 call is so that there can never be more tickets ordered than the specified maximum on the ticketype or the event. If I would get the two sums in php and than write if my php calculations are okay there will be time between the getting of sums and writing the new value and possibly there will be more tickets ordered than allowed.

Maybe I'm looking for the wrong solution and maybe I should do more homework, any direction is appreciated, a fully working query would be most awesome :)

I mentioned laravel because it uses eloquent databases queries but I can use raw SQL when I want so no problem if the solution is raw SQL.

Thank you in advance!

Update: (Got it working with help from mentioned question)

INSERT into ticketsorders (order_id, tickettype_id, event_id, amount) 
SELECT '7', '23', '1', '10'
FROM dual
WHERE ((SELECT COALESCE(SUM(amount), 0) FROM ticketsorders WHERE tickettype_id = 23) + '10' ) <= (SELECT maxtickets from tickettypes where id = '23') 
AND ((SELECT COALESCE(SUM(amount), 0) FROM ticketsorders WHERE event_id = 1) + '10' ) <= (SELECT maxtickets from events where id = '1')
LIMIT 1

only uglieness I had to add was event_id to ticketsorders, we learned in school that you shouldnt do this because its available throug relation event->tickettype->ticketsorder but for now this makes quering for event objects alot easier.

Final update concerning laravel:

after adding in the vars and wrapping the sql in a raw statement like this:

$sql = "INSERT into ticketsorders (order_id, tickettype_id, event_id, amount) SELECT $order->id, $tickettype->id, $eventid, $val FROM dual WHERE ((SELECT COALESCE(SUM(amount), 0) FROM ticketsorders WHERE tickettype_id = $tickettype->id) + $val ) <= (SELECT max from tickettypes where id = $tickettype->id) AND ((SELECT COALESCE(SUM(amount), 0) FROM ticketsorders WHERE event_id = $eventid) + $val ) <= (SELECT max from events where id = $eventid) LIMIT 1";

$testupdate = DB::insert(DB::raw("$sql"));

I found out that the query always returns 1 which means true, even if the insert doesn't go through because the query still ran successfully. Rather than breaking my head on this issue trying to get the actual result I decided to do a new select and see if it succeeded and base my return message to the user on the intended insert compared to the select.

Thank you for the comments they helped a lot

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sjoerd
  • 167
  • 1
  • 9
  • Are you saying you want to 'search' the database AND insert at the same time if the total order of tickets is smaller? If so - you cannot do that. It must be "search" then "insert" as two separate queries. – Laurence May 19 '14 at 10:03
  • so a simple (insert when sum(randomfield) <= 100) is not possible? Than how would you enforce this scenario? – Sjoerd May 19 '14 at 10:46
  • It might be possible - check this answer out: http://stackoverflow.com/q/6854996/1317935 – Laurence May 19 '14 at 10:48
  • But if you wanted to - a better option is probably a table lock - so no one else can buy tickets at the same time. – Laurence May 19 '14 at 10:48
  • updated question with working example, im no fan of table locking so i try to avoid is as much as possible, ill fire up a test of alot updates this weekend to see if i can overshoot the maxtickets – Sjoerd May 19 '14 at 15:29

0 Answers0