Odd SELECT queston???

[Reestit Mutton]

Hi,

I'm currently learning the ropes with CSS, PHP, MYSQL, Javascript etc...
by redesigning my website as a database driven site.

Okay, so I'm skilled at perl, data manipulation and data analysis and
I've done a fair bit of (non CSS) HTML in the past and know what a
relational database is. However, I'm having problems trying to formulate
a particular MYSQL query (maybe I just don't know the lingo or maybe
I've designed my data incorrectly) and wonder if anyone can shed some
light for me.

Basically, I'm trying to put together a retailers list on my site that
is classified according to the types of products that they sell. The
product list is hierarchically structured (not a flat list) so e.g. one
portion of my retailer type structure looks something like this:

Telecoms:
Mobiles:
Contract
PAYG
SIM Free
Landline:
Services
Handsets
Broadband:
Services
Hardware

Now, I intend to have a menu system on my website which exactly mirrors
my retailer type structure that allows the user to look up a list of
retailers based on the type of product/service that they are after.
However, if the user selects a menu item that relates to an incompletely
specified type (e.g. "telecoms" or "telecoms.mobil es" as opposed to
"telecoms.mobil es.contract") I would like my site to list the superset
of companies that the user's selection relates to e.g. ALL telecoms
related companies or ALL mobile related companies.

Currently, my MYSQL table for retailers simply has a flat list of
(binary valued) ENUM keys, one for each fully-specified LEAF on my
retailer type tree. However I have named these variables in a consistent
manner thus

RMTelMobContrac t
RMTelMobPAYG
RMTelMobSimFree
RMTelLandServic e
RMTelLandHandse t
RMTelBBService
RMTelBBHardware

is the subset of ENUM keys associated with the different types of
telecoms related retailers in the above example.

I was hoping to be able to say, "if I ask for RMTelMob companies, please
return any records where RMTelMob* is 'Y'" where RMTelMob* implies any
key that begins with "RMTelMob".

I know that I can just create extra ENUM keys for each branch in the
tree but I wondered if there was a way of effectively calculating this
on the fly instead, relying on the fact that I adhere to a consistent
naming structure - in essence it looks like I want to find ALL keys that
are extensions of the more general key and then select the record if at
least one of them is marked as true.

Alternatively, is there a better way of designing the data in this table
to make it (a) more consistent with the relational paradigm (b) easier
to query in this manner (I guess (b) would follow from (a) in any case)?

cheers,
RM

[Reestit Mutton]

UPDATE: Odd SELECT queston

I've had a good long think about this and wonder if it could be done as
follows...

Firstly, create a table describing the heirarchy of the menu items,
where ALL routes through the menu hierarchy are specified as follows:

Ancestor
Descendent
Route Length

Thus

ALL, RMTel, 1
ALL, RMTelMob, 2
ALL, RMTelMobContrac t, 3
ALL, RMTelMobPAYG, 3
ALL, RMTelMobSIMFree , 3
RMTel, RMTelMob, 1
RMTel, RMTelMobContrac t, 2
RMTel, RMTelMobPAYG, 2
RMTel, RMTelMobSIMFree , 2
RMTelMob, RMTelMobContrac t, 1
RMTelMob, RMTelMobPAYG, 1
RMTelMob, RMTelMobSIMFree , 1

describes the "mobiles" branch of my menu heirarchy:

ALL -- Telecoms ----- Mobiles ----- Contract
| |
| |-- PAYG
| |
| |-- SIM Free
|
|-- Landline ----- Services
|
|-- Handsets

Thus any top level item (such as ALL in this instance) is identifiable
by never existing in the "Descendent s" column and any fully specified
item is identifiable by never existing in the "Ancestor" column.

So, for any particular menu item, all I have to do is look up ALL its
descendents with the maximum route length and, if any exist, check that
at least one of them is marked as "TRUE" in the retailers table, which
still contains a flat list of item types (all the bottom level leaves of
the menu heirarchy). The question is, if the SELECT statement on the
menu heirarchy table returns a list of possible bottom level leaves, how
can I run a query on this list (as field identifiers in the retailers
table) such that at least ONE of the listed fields holds true?

e.g. SELECT * FROM table WHERE [any one of {a,b,c...}] = "TRUE"

where {a,b,c...} is the list returned from the menu heirarchy query

Is this kind of query doable in MYSQL? or do I still not have an
appropriate relational design for my needs?

cheers
RM

[Bill Karwin]

Re: UPDATE: Odd SELECT queston

Reestit Mutton wrote:[color=blue]
> Firstly, create a table describing the heirarchy of the menu items,
> where ALL routes through the menu hierarchy are specified[/color]

This is exactly the method described in "SQL for Smarties" by Joe Celko
for representing hierarchical structures in an RDBMS. I used it in a
recent project, and it worked great!

For the ancestor and descendant, I made these fields foreign keys into
the "flat" master table of resources. I could get the details for the
retailer by doing a join back to that table.

select r.retailer_name
from retailers r inner join path_enum p
on r.retailer_id = p.descendant_id
where p.ancestor_id = ?

The above fetches all children of ? (which can be specified as a query
parameter).

To find only leaf nodes of the tree, do a further join where the id in
the path_enum never appears as an ancestor of another retailer:

select r.retailer_name
from retailers r inner join path_enum p
on r.retailer_id = p.descendant_id
left outer join path_enum p2
on p.descendant_id = p2.ancestor_id
where p.ancestor_id = ?
and p2.ancestor_id is NULL
[color=blue]
> The question is, if the SELECT statement on the
> menu heirarchy table returns a list of possible bottom level leaves, how
> can I run a query on this list (as field identifiers in the retailers
> table) such that at least ONE of the listed fields holds true?[/color]

I guess you mean that if any one of the retailers has a TRUE value in
some attribute field, then return all of them, otherwise return none of
them? Or return only those that have a TRUE value?

This would provide the latter case:

select r1.*
from retailers r1
where r1.retailer_id in (
select IF(r.some_attri bute = 'TRUE', r.retailer_id, NULL)
from retailers r2 inner join path_enum p
on r2.retailer_id = p.descendant_id
left outer join path_enum p2
on p.descendant_id = p2.ancestor_id
where p.ancestor_id = ?
and p2.ancestor_id is NULL
)

Regards,
Bill K.

[Reestit Mutton]

Re: UPDATE: Odd SELECT queston

Bill Karwin wrote:[color=blue]
> Reestit Mutton wrote:
>[color=green]
>> Firstly, create a table describing the heirarchy of the menu items,
>> where ALL routes through the menu hierarchy are specified[/color]
>
>
> This is exactly the method described in "SQL for Smarties" by Joe Celko
> for representing hierarchical structures in an RDBMS. I used it in a
> recent project, and it worked great!
>[/color]

Thanks Bill - the early examples in your response demonstrated the
traversal of the tree to find the leaf nodes. However, unless I'm
mistaken, your final example doesn't seem to correspond to my problem.

Basically my problem is as follows...

$query = "SELECT DISTINCT P.RMDescendent
FROM menu_hierarchy P LEFT OUTER JOIN menu_hierarchy P2
ON (P.RMDescendent = P2.RMAncestor)
WHERE P.RMAncestor = '$MenuItem' AND
P2.RMAncestor IS NULL";

Returns a distinct list of fully specified leaf nodes below a selected
menu item, denoted by the variable "$MenuItem" , from my menu hierarchy.
(Thanks - your code was helpful here)

thus, in the following example menu structure,

ALL -- Telecoms ----- Mobiles ----- Contract
| |
| |-- PAYG
| |
| |-- SIM Free
|
|-- Landline ----- Services
|
|-- Handsets


if $MenuItem = "Landline", the above code would return a list of length
2: ["Services", "Handsets"]. Alternatively, if $MenuItem = "Telecoms",
the above code would return a list of length 5: ["Contract", "PAYG",
"SIM Free", "Services", "Handsets"].

Each item in the resulting list should directly correspond to a field
name (for a field of type ENUM with allowable values of 'TRUE' &
'FALSE') in my retailers list. i.e. each record in th eretailers table
contains a single field of type ENUM for each leaf node in the
menu_hierarchy table where the field names are used as values in the
menu_hierarchy table.

What I would like to do is pull all records from the retailers table
where at least one of these fields is marked as TRUE. This would
correspond to e.g. selecting the union of all retailers that sell mobile
related products regardless of whether it is a contract, payg or sim
free handset if I simply choose to select "Mobile" in the menu. However,
if I select "PAYG" in the menu, it will just display those retailers
that sell PAYG mobile handsets.

All retailers are simply marked up for whether or not they offer each
specific type of product. As I may decide to reorganise the menu
structure at a later date I would rather not hard code the relationship
in the retailers table by means of including pre-calculated additional
fields for branches. i.e. the menu_hierarchy table is intended to
describe the structure so that I can find the subset of leaves the
correspond to each possible menu item.

Hopefully this will clarify what I'm after.

cheers,
RM

[Bill Karwin]

Re: UPDATE: Odd SELECT queston

Reestit Mutton wrote:[color=blue]
> Each item in the resulting list should directly correspond to a field
> name (for a field of type ENUM with allowable values of 'TRUE' &
> 'FALSE') in my retailers list. i.e. each record in th eretailers table
> contains a single field of type ENUM for each leaf node in the
> menu_hierarchy table where the field names are used as values in the
> menu_hierarchy table.[/color]

Ah, now I understand what you're trying to do. No, you can't do that. ;-)

What you're trying to do is to make a join between data in one table and
the _name_ of a column. So the column in the join criteria would have
to vary on every row of the result set. This is something SQL does not
support; the optimizer must know the tables and columns in the various
clauses, and these tables and columns must not vary (otherwise the
optimizer can't figure out at parse-time necessary things about the
query, such as what indexes might be of benefit).

Instead of storing True/False values in columns for each product
category, you should store the product category names for which a given
retailer has a True value. Make another dependent table for this:

CREATE TABLE retailer_produc t_category (
retailer_id integer not null references retailers,
product_categor y varchar(100) not null
);

So for example, if a given retailer #327 has True values in fields
RMTelLandServic e and RMTelBBService, and False for all the others, you'd
insert two records to retailer_produc t_category:

INSERT INTO retailer_produc t_category VALUES
(327, 'RMTelLandServi ce'),
(327, 'RMTelBBService ');

Don't insert any records for the False values.

Now your categories are data values, not column names, and you can get
the list of retailers you want by performing a join:

Returning the retailers who sell products in a given category is done as
follows:

SELECT DISTINCT R.retailer_name
FROM retailers R INNER JOIN retailer_produc t_category C
ON R.retailer_id = C.retailer_id
WHERE C.product_categ ory = ?

And returning the leaf-nodes of your menu heirarchy who correspond to
retailers who sell products in a given category is the above query
combined with the path enumeration query we did before.

SELECT DISTINCT R.retailer_name
FROM retailers R INNER JOIN retailer_produc t_category C
ON R.retailer_id = C.retailer_id
INNER JOIN path_enum P
ON R.retailer_id = P.descendant_id
LEFT OUTER JOIN path_enum P2
ON P.descendant_id = P2.ancestor_id
WHERE P.ancestor_id = ? AND P2.ancestor_id IS NULL
AND C.product_categ ory = ?

The DISTINCT operation provides your "collapsing ", where if any of the
desired categories applies to the retailer, the retailer is included in
the final result set--once.

Storing the product categories in another table also enables you to
create new product categories without adding columns to your retailer
table. And there's no need for the product category names to be tied to
your menu structure. So it further frees you from hardcoded categories
and menu structure.

Regards,
Bill K.

[Reestit Mutton]

Re: UPDATE: Odd SELECT queston

Bill Karwin wrote:[color=blue]
> Reestit Mutton wrote:
>[color=green]
>> Each item in the resulting list should directly correspond to a field
>> name (for a field of type ENUM with allowable values of 'TRUE' &
>> 'FALSE') in my retailers list. i.e. each record in th eretailers table
>> contains a single field of type ENUM for each leaf node in the
>> menu_hierarchy table where the field names are used as values in the
>> menu_hierarchy table.[/color]
>
>
> Ah, now I understand what you're trying to do. No, you can't do that. ;-)
>[/color]

<SNIP useful solution>

Thanks Bill, my problem is now solved thanks to you. I have also learned
that little bit more about the practicalities of relational database
design. (the theory was never a problem, I was just lacking the
practical experience)

cheers,
RM
--
Boots technology bargains with 10% off + triple advantage card points
e.g. iPod photo 40GB for £281, Nikon Coolpix 4100 for £117
where can I find more details (incl. other bargains & discount codes)?

http://www.users.totalise.co.uk/~lauriem/latest_additions.html#0702200501