little SQL help

[dmmh]

I have a table which stores different lenses. Some lenses are variations of other lenses. They are all stored in the same table and I differentiatie between the parent and the child via 2 columns in that table: 'multiple' and 'is_child'.

What I do now is query for all lenses in the database and ignore the childs:

SELECT * FROM lenses
WHERE id IS NOT NULL AND is_child IS NULL

this list is sorted in natural order via PHP scripts based upon the values of 3 different fields in the table

I can not query for the childs, as they will return the lenses as unique ones, whereas I want to display them as subversions/ variations right below its parent on a HTML page generated via the script.

Now I achieve this via a lot of complicated PHP loops and maybe I can optimize this much more by 'simply' adjusting the query.

problems I encounter:
- Fetching all rows and sorting them in natural order via a SQL statement will not ensure that the childs are displayed directly below their respective parents, but I do need the natural sorting.
- the childs also need to be sorted natural below the parent, but the 'parent' result set should maintain its own sorting order

Basically I need:
1 select and natural sort all lenses in the table until a row is encountered where the value meets a certain requirement (multiple='1').
2 If such row is encountered, search for rows where is_child has the same value as the row which met the requirement and natural sort those in return (but on its own level, so not in order with the primary lenses)!)
3 skip back to the row after where the requirement was met and continue step 1+2 until the end of the table is met

I dont know if I can do this best with a procedure/ function or via a regular query. Havent used procedures/ funtions yet...

[^misantropia^]

What you want (if I understand you right) is not exactly impossible in SQL but you'll end up with a monster query: slow, difficult to maintain and prone to break whenever you upgrade or migrate your database.

Do it in the code. You'll be a happier person for it.

[bitWISE]

Yea. I would just query for a list of parents and when you display each parent run another query to get the children for that parent and display those.

Cutting back on the number of queries may improve performance in most cases but large, joining queries also create more records locks as the number of users grow.

[dmmh]

well yeah, but thats just more queries. The PHP loops are driving me fucking insane and are massive. Just wanted something that looked prettier

[bitWISE]

Isn't it only two while loops, one inside the other?

[PhoeniX]

It sounds like it would be easier to split the data into a couple of tables.

i.e.:

Lenses Table
ID
Manufactuer.ID
Details

Child table
ID
LenseID (an ID in the lenses table)
Details

You could then use a JOIN statement to get child data. A for loop probably isn't the most optimised way here, but I can't think of an alternative:

Code: Select all

for each item in the lenses table as $lense
	select * from Lenses join Child on Lenses.ID = Child.LenseID AND Lenses.ID = $lense['ID'];
end for

note: I may have used the wrong JOIN type, I get confused. See: http://www.wellho.net/mouth/158_MySQL-L ... -JOIN.html

[4days]

it's more likely that i've just read it too quickly and gotten the wrong end of the stick than your description being garbled, but i didn't really understand it. if you're trying to manage hierarchical data about these lenses then what Pho said sounds good. here are a couple of articles on storing hierarchical data that may or may not be useful:

http://www.sitepoint.com/article/hierar ... a-database
http://www.brianhaveri.com/?id=42

[Foo]

well yeah, but thats just more queries. The PHP loops are driving me fucking insane and are massive. Just wanted something that looked prettier

-----
--select non-child lenses
--put returned set into array 1
--begin loop
----for each array 1 item, select its children
----put returned set into array 2
----output current array 1 item
----output entire array 2
--end loop
-----

[dmmh]

Isn't it only two while loops, one inside the other?

if I wouldnt need natural sorting on the parent array and natural sorting on the children, then yes

[dmmh]

well yeah, but thats just more queries. The PHP loops are driving me fucking insane and are massive. Just wanted something that looked prettier

-----
--select non-child lenses
--put returned set into array 1
--begin loop
----for each array 1 item, select its children
----put returned set into array 2
----output current array 1 item
----output entire array 2
--end loop
-----

well yeah, something like that is what I have. But the natural sorting is preceded by 300 lines of code to determine which item to display first. Thats set1. Since set2 uses the same parameters to determine how the second set should be ordered, I will have 300 lines of the exact same code in my script on 2 places. It is in turn put in a an array, then natsort is used, then I need to fetch the field values. Trust me, it doesnt look pretty

it's not that it aint working, I just need a better way

[dmmh]

it's more likely that i've just read it too quickly and gotten the wrong end of the stick than your description being garbled, but i didn't really understand it. if you're trying to manage hierarchical data about these lenses then what Pho said sounds good. here are a couple of articles on storing hierarchical data that may or may not be useful:

http://www.sitepoint.com/article/hierar ... a-database
http://www.brianhaveri.com/?id=42

ow I know how to use this, there are already 5 table INNER JOINs in the query, I left these out to simplify things.

But it doesnt work really all that well putting minor variations of items in a different table. The JOIN would merge values and logic would be lost. Minimal aperture of the lens in table1 may differ from its child in table2, as well as all sorts of other properties such as weight, size, coating, introduction year, end of life year etc etc. Using a seperate table is the least optimal solution, as the result would be harder to interpret. Not to mention data redundancy. In the examples, the different tables really hold different properties. I would have to maintain 2 50+ field tables which both hold the same properties. It's just not very effective and poor database design if you ask me.

But thanks for all your ideas, at least I dont feel like a fool for doing it the way I am doing it

[bitWISE]

Here is a tip for you. Instead of using PHP to sort the data, build a dynamic sort by string in PHP and append it to your query. Then you just display the data as you loop through it.

[^misantropia^]

You can farm out some of the work to the database. MySQL doesn't support natural order sorting natively, but "ORDER BY fieldname + 0" does the trick quite nicely. That should bring it back to one outer and one inner loop, no?

[phoq]

Don't know what database server you're using (I'm assuming MySQL 5?), but I'd recommend using stored procedures for this kind of stuff. It might not be the brightest idea in terms of database independancy, but it's certainly the best solution performance-wise plus they're not that difficult to understand. If you want I can have a look at setting up the basics for the routine for you. Just send me an e-mail with an sql-dump (if you're using MySQL) of the database and an explanation of which data you want from which table, and I'll have a look when I got the time.

[dmmh]

that's what I reckoned yes. I'll pm/ mail you this weekend, if I find the time. Thanks

[phoq]

I'll pm you my e-mail address, since the e-mail icon thingy doesn't seem to be doing what it's supposed to do.

[dmmh]

Here is a tip for you. Instead of using PHP to sort the data, build a dynamic sort by string in PHP and append it to your query. Then you just display the data as you loop through it.

well I need to.

I'll explain why.

I sort the lenses like this, from low to high:

$str[$x] = $focal[$a]' '.$maximum_aperture.' '.$introduction_year;

$focal is the nastiest dilimiter here. Old lenses were sometimes denoted in cm instead of mm. I store the lens unit denotation in a sperate field. Then, we also need to take into account zoomlenses, which have a variable focal length, whereas primes have a fixed focal length.

So:

if ($row['focal_units'] === 'cm'){
$sort_focal[$a] = ($row['min_focal_length']*10); //focal length in mm
}else{
$sort_focal[$a] = $row['min_focal_length'];
}
if ($row['min_focal_length'] !== $row['max_focal_length']){
$sort_focal[$a] .= '-'.$row['max_focal_length']; //(always in mm, Nikon is crazy...)
}
$display_focal[$a] = $row['min_focal_length']; //focal length original

$display_name[$a] = $display_focal[$a].' '.$row['focal_units'].'f/'.$maximum_aperture.' '.$introduction_year;
$sort_names[$a] = $sort_focal[$a].' '.$maximum_aperture.' '.$introduction_year;
natsort($sort_names);

loop thru, result:
2.5 cm f/1.4 1959
25 mm f/1.4 1959
25 mm f/1.4 1962
25 mm f/2.0 1995
25 mm f/2.5 1968
25 mm f/2.5 1977

sigh....

[Foo]

well yeah, something like that is what I have. But the natural sorting is preceded by 300 lines of code to determine which item to display first. Thats set1. Since set2 uses the same parameters to determine how the second set should be ordered, I will have 300 lines of the exact same code in my script on 2 places. It is in turn put in a an array, then natsort is used, then I need to fetch the field values. Trust me, it doesnt look pretty

it's not that it aint working, I just need a better way

Sounds like this monstrosity of code could do to be put into a function so you can call it at both points.

However... 300 lines of code? Why do you need 300 lines of code to determine what item to show first?

I just eh.... I think there's something ELSE that's fundamentally wrong with the way you're going about this that is leading you to think you need to change the way you need to do THIS particular part of the system... and it sounds like the problem lies somewhere else rather than in this particular obstacle you're trying to overcome... the issues you're having here sound like a symptom and not the actual problem.

I don't mean that to be a troll either... Just the impression I'm getting so far

[4days]

what foo said, and what's already been said. looks like the data needs to be better organised.

[mjrpes]

I haven't used this technique in awhile, but have you thought about using table aliases to create a virtual join between your table? Something like, assuming the field 'is_child' isn't really boolean but contains the id of the row it is a 'child' of,

SELECT alias1.parent_camera, alias2.parent_camera FROM my_table AS alias1 LEFT JOIN my_table AS alias2 ON alias1.parent_id = alias2.is_child WHERE alias1.is_child IS NULL ORDER BY [natural order for alias1], [natural order for alias2]

You would then get multiple rows of alias1.parent_camera where it has child rows within the same table, but at least it would all be in one (ugly) query.

[Dave]

What foo said... but if I remember right I had a similar issue with the forum I wrote and trying to keep categories/sub forums properly grouped. If you don't have much data, just pull everything from the table and do a loop. It shouldn't be that complex. From the sounds of your schema, you need another table to handle relationships instead of doing it in fields in the table. It's much easier to visualize that way.