Painful SQL, More Shiny Latex and Uneducated Drivel

Painful SQL

So today I was working on one of the contracts I've currently got going - the task itself is not large, but it has a few somewhat challenging components. I (kind of) conquered one of them today. An immensely complicated SQL query that involves sorting the returned rows by how the relative ratios of protein, carbohydrates and fats in each item retrieved differ from a user's goal. Furthermore, it has to take into consideration that it is less desirable for the bulk of the difference to be in one category instead of spread out across all three. Somehow, with a combination of standard deviation equations and some handy experimentation on my part, I managed to come up with a solution - hooray for first year statistics class! Still, thanks to the fact that you can't use aliases for much within the query itself (can't multiply two aliases together, etc) I ended up with an absolutely HIDEOUS query. Surprisingly enough, I had very few syntactical issues, and none of them came from the part to do with value on which the results were sorted. Enjoy:

SELECT it.items as name, it.grams as grams, it.portion as portion,  it.calories as calories, it.grams_p as protein, it.grams_c as carbs,  it.grams_f as fat, it.grams_sat_f as saturated, (SQRT(((((14 -  (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9))))  - (((14 - (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))) + (56 - (it.grams_c*4/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) + (30 -  (it.grams_f*9/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f *  9)))))/3)) * ((14 - (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))) - (((14 - (it.grams_p*4/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) + (56 -  (it.grams_c*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9))))  + (30 - (it.grams_f*9/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))))/3))) + (((56 - (it.grams_c*4/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) - (((14 -  (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9)))) + (56 - (it.grams_c*4/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) + (30 -  (it.grams_f*9/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f *  9)))))/3)) * ((56 - (it.grams_c*4/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))) - (((14 - (it.grams_p*4/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) + (56 -  (it.grams_c*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9))))  + (30 - (it.grams_f*9/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))))/3))) + (((30 - (it.grams_f*9/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) - (((14 -  (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9))))  + (56 - (it.grams_c*4/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))) + (30 - (it.grams_f*9/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))))/3)) * ((30 - (it.grams_f*9/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))) - (((14 -  (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9))))  + (56 - (it.grams_c*4/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))) + (30 - (it.grams_f*9/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))))/3))))/2) * ((14 -  (it.grams_p*4/((it.grams_p * 4) + (it.grams_c * 4) + (it.grams_f * 9))))  + (56 - (it.grams_c*4/((it.grams_p * 4) + (it.grams_c * 4) +  (it.grams_f * 9)))) + (30 - (it.grams_f*9/((it.grams_p * 4) +  (it.grams_c * 4) + (it.grams_f * 9)))))) AS sortorder FROM items it LEFT  JOIN intake int_f ON (it.id_foods=int_f.id_foods and int_f.Date >=  DATE_SUB(CURDATE(),INTERVAL 4 DAY) and int_f.user_id='1') LEFT JOIN  intake int_fg ON (it.id_groups=int_fg.id_groups and int_fg.Date >=  DATE_SUB(CURDATE(),INTERVAL 2 DAY) and int_fg.user_id='1') WHERE  int_f.id_intake IS NULL and int_fg.id_intake IS NULL and ((IF(it.meal_id=0,it.meal_id,(SELECT SUM(sint_f.id_intake) + SUM(sint_fg.id_intake) FROM meal_ingredients ming LEFT JOIN items mit ON (ming.item_id = mit.id_items) LEFT JOIN intake sint_f ON (mit.id_foods=sint_f.id_foods and sint_f.Date >= DATE_SUB(CURDATE(),INTERVAL 4 DAY) and sint_f.user_id='$user') LEFT JOIN intake sint_fg ON (mit.id_groups=sint_fg.id_groups and sint_fg.Date >= DATE_SUB(CURDATE(),INTERVAL 2 DAY) and sint_fg.user_id='$user') WHERE ming.meal_id=it.meal_id))) IS NULL or it.meal_id='0') ORDER BY sortorder ASC LIMIT 15

More Shiny Latex

On another note, I did another latex study. It's nice, you know, to have a blog that no one reads - means I don't have to be self conscious for using latex fetish models for studies. Today's was Susan Wayland. Still needs work, and I threw the face and hair on there rather quickly because I was getting tired. Not that I should be making excuses.

Uneducated Drivel

Oh! Today's Art History lecture was very interesting, once again. It pains me that I've missed two classes already thanks to the weather (I haven't been keen on driving through snowstorms for an evening elective). It's not common for me to regret missing a class or two. Today we covered various interpretations of Baroque art; Spanish, Flemish and Dutch. A lot of beautiful pieces, although I didn't find myself particularly fond of the all-too everyday paintings of Vermeer. I mean, I know they hold deeper meaning in raising the average person to a piece of interest, but it just doesn't do much for me. Even some of the still lifes from the same period and region seemed more intriguing to me. Then again, it may have been the particular pieces our professor showed us - I do remember finding The Girl with the Pearl Earring somewhat interesting.

But oh jeez. Las Meninas, by Velázquez? I have no words. The amount of depth in the painting, and how the mere presence of a canvas blocking off the far left brings the viewer into the piece is astonishing. By adding that border element, it seems to knock away all other edges and immerse you into the scene. I did appreciate that same sort of element of Vermeer's The Art of Painting, but I think its disregard of the viewer, counters that connection and allows me to detach all too easily.

... But seriously. How does Vermeer know what his own back looks like?