Dataset filtering with infinate options

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

This is kind of crazy and a little hard to explain.

Basically I have a table of customers and a table of metadata.

Code: [ Select ]
+-----------+ +-------------+
| Customers | | Metadata    |
+-----------+ +-------------+
| id        | | id          |
| name      | | customer_id |
+-----------+ | key         |
              | value       |
              +-------------+
  1. +-----------+ +-------------+
  2. | Customers | | Metadata    |
  3. +-----------+ +-------------+
  4. | id        | | id          |
  5. | name      | | customer_id |
  6. +-----------+ | key         |
  7.               | value       |
  8.               +-------------+


The data for the fields is supplied by an API that replies with json. Each field may contain more than one value, in which case they will be seperated by a comma.

Right now my code to break up the data looks a little like this:
Code: [ Select ]
customer.each do |key,value|
    if key == CN_CONFIG['primary_key']
        #This is unique to all customers.
        if value == nil || value == ""
            # You have no name!!!!!!!
            break
        end
        if customers.include?(value)
            # This is an update
            update_c = Customer.find_by_name(value)
            customers.delete(value)
        else
            # This is a create
            update_c = nil
            customer_name = value
        end
    end
    if !ignore_fields.include?(key)
        if list_fields.include?(key)
            if value.scan(/,/).size > 0
                # We have a list
                puts "key: #{key} -- #{value} "
                value.split(',').each do |v|
                    data_set.push({:key=>key,:value=>v.to_s.downcase.strip})
                    puts "  value: #{v.to_s.downcase.strip}"
                end
            else
                data_set.push({:key=>key,:value=>value.to_s.downcase.strip})
                puts "key: #{key} | value: #{value.to_s.downcase.strip}"
            end
        else
            data_set.push({:key=>key,:value=>value.to_s.downcase.strip})
            puts "key: #{key} | value: #{value.to_s.downcase.strip}"
        end
    end
end
  1. customer.each do |key,value|
  2.     if key == CN_CONFIG['primary_key']
  3.         #This is unique to all customers.
  4.         if value == nil || value == ""
  5.             # You have no name!!!!!!!
  6.             break
  7.         end
  8.         if customers.include?(value)
  9.             # This is an update
  10.             update_c = Customer.find_by_name(value)
  11.             customers.delete(value)
  12.         else
  13.             # This is a create
  14.             update_c = nil
  15.             customer_name = value
  16.         end
  17.     end
  18.     if !ignore_fields.include?(key)
  19.         if list_fields.include?(key)
  20.             if value.scan(/,/).size > 0
  21.                 # We have a list
  22.                 puts "key: #{key} -- #{value} "
  23.                 value.split(',').each do |v|
  24.                     data_set.push({:key=>key,:value=>v.to_s.downcase.strip})
  25.                     puts "  value: #{v.to_s.downcase.strip}"
  26.                 end
  27.             else
  28.                 data_set.push({:key=>key,:value=>value.to_s.downcase.strip})
  29.                 puts "key: #{key} | value: #{value.to_s.downcase.strip}"
  30.             end
  31.         else
  32.             data_set.push({:key=>key,:value=>value.to_s.downcase.strip})
  33.             puts "key: #{key} | value: #{value.to_s.downcase.strip}"
  34.         end
  35.     end
  36. end


Not really to important to the question though.

What question is, how do you filter upon a table of meta data?
Example say some of the keys listed in the metadata table are: gender, birth_year, siblings

Where:
gender => m or f
birth_year => (any year YYYY)
siblings => (a name, and each customer can have infinite number)

(Btw this isnt the real data lol)

Now What I plan to do is group all the keys together to get all the keys that show up in the metadata table. These turn into field sets, all the values turn into check boxes that have a select menu to include or exclude the criteria.

Now, how do you write a query to return all the customers that meet the criteria?
  • Nightslyr
  • Proficient
  • Proficient
  • Nightslyr
  • Posts: 283

Post 3+ Months Ago

This:

SpooF wrote:
Each field may contain more than one value, in which case they will be seperated by a comma.


Makes me think you should read this: http://dev.mysql.com/tech-resources/art ... ation.html

Comma-separated values in a column is generally a sign you're doing it wrong. This is especially true if the data is relational, and is being used as a key or index.

The search queries themselves will become clear after you normalize your data.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

They values in the columns are not comma separated, only the values that are returned by the API which populates the table. Do to the nature that there is an infinite number of possible keys returned and each key may have more than one value associated with it Im doing a key value pair system to store my information.

You can see that by reading the code presented above and the excerpt below:
Code: [ Select ]
if value.scan(/,/).size > 0
  # We have a list
  puts "key: #{key} -- #{value} "
  value.split(',').each do |v|
    data_set.push({:key=>key,:value=>v.to_s.downcase.strip})
    puts " value: #{v.to_s.downcase.strip}"
  end
else
  data_set.push({:key=>key,:value=>value.to_s.downcase.strip})
  puts "key: #{key} | value: #{value.to_s.downcase.strip}"
end
  1. if value.scan(/,/).size > 0
  2.   # We have a list
  3.   puts "key: #{key} -- #{value} "
  4.   value.split(',').each do |v|
  5.     data_set.push({:key=>key,:value=>v.to_s.downcase.strip})
  6.     puts " value: #{v.to_s.downcase.strip}"
  7.   end
  8. else
  9.   data_set.push({:key=>key,:value=>value.to_s.downcase.strip})
  10.   puts "key: #{key} | value: #{value.to_s.downcase.strip}"
  11. end

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 74 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.