Stored procedure using dynamic SQL statement stored in database column

Question

I have a table called Coupon.

This table has a column called query which holds a string.

The query string has some logical conditions in it formatted for a where statement. For example:

coupon1.query
=> " '/hats' = :url "
coupon2.query
=> " '/pants' = :url OR '/shoes' = :url "

I want to write a stored procedure that takes as input 2 parameters: a list of Coupon ids and a variable (in this example, the current URL).

I want the procedure to look up the value of the query column from each Coupon. Then it should run that string in a where statement, plugging in my other parameter (current url), then return any Coupon ids that matches.

Here's how I would expect the procedure to behave given the two coupons above.

Example 1: 
* Call procedure with ids for coupon1 and coupon2, with @url = '/hats'
* Expect coupon1 to be returned.

Example 2: 
* Call procedure with ids for coupon1 and coupon2, with @url = '/pants'
* Expect coupon2 to be returned.

Example 3: 
* Call procedure with ids for coupon1 and coupon2, with @url = '/shirts'
* Expect no ids returned. URL does not match '/hats' for coupon1, and doesn't match '/pants or /shoes' for coupon2. 

It's easy to test these out in ActiveRecord. Here is just example 1.

@url = '/hats'
@query = coupon1.query 
# "'/hats' = :url"
Coupon.where(@query, url: @url).count
=> 2   
# count is non-zero number because the query matches the url parameter. 
# Coupon1 passes, its id would be returned from the stored procedure.
'/hats' == '/hats'

@query = coupon2.query 
# " '/pants' = :url OR '/shoes' = :url "
Coupon.where(@query, url: @url).count
=> 0
# count is 0 because the query does not match the url parameter. 
# Coupon2 does not pass, its id would not be returned from the stored procedure.
'/pants' != '/hats', '/shoes' != '/hats'

You could write this as a loop (I'm in ruby on rails with activerecord) but I need something that performs better - I could potentially have lots of coupons so I can't just check each one directly with a loop. The queries contain complex AND/OR logic so I can't just compare against a list of urls either. But here's some code of a loop that is essentially what I'm trying to translate into a stored procedure.

# assume coupon1 has id 1, coupon2 has id 2
@coupons = [coupon1, coupon2]
@url = '/hats'
@coupons.map do |coupon|
    if Coupon.where(coupon.query, url: @url).count > 0
        coupon.id
    else
        nil
    end
end
=> [1, nil]

Answer 1

Ok, I've been pondering this one.

Big picture:

A. You have a @url you want to search for to find a match among many potential Coupons

B. A coupon has a URL that might match @url

If that's the true extent of the problem, I think you've really over-complicated things.

coupon1.query
=> ["/hats"]
coupon2.query
=> ["/pants", "/shoes"]

@url = '/hats'

Coupon.where('FIND_IN_SET(:url, query) <> 0')

Or something similar, I'm not a mySQL user myself.

However, this is very possible to achieve and may even have a much better ActiveRecord way to do the query.

---

UPDATE

Ok, I'm missing something. I can't actually reproduce this in console.

@url = '/hats'
@query = coupon1.query 
# "'/hats' = :url"
Coupon.where(@query, url: @url).count
> SELECT * FROM 'coupons' WHERE ( '/hats' = '/hats' )

As you can see from the select statement, this will always return all records. It's the same as writing SELECT * FROM 'coupons' WHERE ( true )

How are you actually performing a valid query?

Sorry to post this in my answer, I wanted good formatting.

If I've got something wrong here, maybe we need to move this to a chat room.

I think you have just enough reputation for me to invite you to a room.

---

UPDATE2

Since you have to compare @query to each record individually, I think you'll have to loop.

But, I don't think you need to use Coupon.where to accomplish this since you are only comparing one record at a time.

@coupons.map do |coupon|
  # don't bother putting nil in the array
  next unless coupon.query == @url
  
  coupon.id
end

However, your original question was about performance when scaled, and you know you aren't going to solve that with a loop.

Maybe JSONB instead of String so that you could actually do some SQL.

But, even with JSONB, this is still complicated by wanting your conditions to be evaluated properly.

{
  "url": {
    "AND": ["/hats", "/shoes"],
    "OR": ["/pants"]
  },
  "logged_in": true,
  "is_gold_member": false
}

{
  "logged_in": false,
  "url": "/hats"
}

{
  "url": {
    "OR": ["/pants", "/shoes"]
  }
}

Ultimately, I think what you're doing with query attributes is going to continue to be your stumbling block. It's very clever, but it's not simple.

If it were my app, I think I would go back to considering my use case and try to find a different strategy to map specific coupons to specific parameters in a more on-the-rails way.