StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The name value pair solution can be done within the database.</p> <p>In this example there are 4 tables, person, requirement, pet, petstat</p> <p>Person and Pet each have an id and a name.<br> Each person also has one or more requirements. Requirements have a name, comparison, and a value.<br> Each pet has one or more PetStats which consist of a name and a value</p> <p>with this setup, the short haired dogs could be found with a query like :</p> <pre><code>select * from pet, petstat where pet.id = petstat.petId and petstat.name = 'hair' and petstat.value = 'short' and petId in (select petId from petstat where name='type' and value = 'dog') </code></pre> <p>The following query will match people to pets when all of the person's requirements are met by a pet.</p> <pre><code>select person.name, pet.name from person, pet where (select count(*) from requirement where requirement.personid = person.id) = (select count(*) from requirement, petstat where requirement.comparison = 'eq' and requirement.name = petstat.name and requirement.value = petstat.value and requirement.personId = person.id and petstat.petid = pet.id) + (select count(*) from requirement, petstat where requirement.comparison = 'lt' and requirement.name = petstat.name and requirement.value > petstat.value and requirement.personId = person.id and petstat.petid = pet.id) + (select count(*) from requirement, petstat where requirement.comparison = 'gt' and requirement.name = petstat.name and requirement.value < petstat.value and requirement.personId = person.id and petstat.petid = pet.id); </code></pre> <p>The less than and greater than comparisons could be handled better by adding numeric fields to the requirement and petstat tables and adding more pieces to the query, but this gives a good stat.</p> <p>Here are the inserts to create the test data.</p> <pre><code>delete from person; insert into person (id, name) values (1, 'Joe'); insert into person (id, name) values (2, 'Bill'); insert into person (id, name) values (3, 'Erik'); insert into person (id, name) values (4, 'Mike'); delete from pet; insert into pet (id, name) values (1, 'spot'); insert into pet (id, name) values (2, 'mittens'); insert into pet (id, name) values (3, 'rover'); delete from requirement; insert into requirement (personid, name, comparison, value) values (1, 'type', 'eq', 'dog'); insert into requirement (personid, name, comparison, value) values (1, 'color', 'eq', 'black'); insert into requirement (personid, name, comparison, value) values (2, 'type', 'eq', 'fish'); insert into requirement (personid, name, comparison, value) values (3, 'type', 'eq', 'dog'); insert into requirement (personid, name, comparison, value) values (3, 'hair', 'eq', 'long'); insert into requirement (personid, name, comparison, value) values (4, 'type', 'eq', 'dog'); insert into requirement (personid, name, comparison, value) values (4, 'weight', 'lt', '30'); insert into requirement (personid, name, comparison, value) values (4, 'weight', 'gt', '20'); delete from petstat; insert into petstat (petId, name, value) values (1, 'type', 'dog'); insert into petstat (petId, name, value) values (1, 'color', 'black'); insert into petstat (petId, name, value) values (1, 'hair', 'short'); insert into petstat (petId, name, value) values (2, 'type', 'cat'); insert into petstat (petId, name, value) values (3, 'type', 'dog'); insert into petstat (petId, name, value) values (3, 'weight', '25'); insert into petstat (petId, name, value) values (3, 'color', 'brown'); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload