Demiel: Danblack: http://pastebin.com/h3DA8fdW
Hoecker: I am not sure how to ask this. I am trying to give an “alias” to a computed value in my SELECT. for example LENGTHfield AS length. so that I can use this length in my WHERE clause later
Besco: Is this sort of thing possible
Reph: My query now looks like this: http://pastie.org/10418473
Borba: Djam90: aliases established in the SELECT clause are not available until the resultset is returned can be used in GROUP BY, ORDER BY and HAVING clauses. Aliases established in the FROM clause are available earlier can be used in the WHERE clause.
Rumery: Djam90, you can repeat the expression in WHERE to get the same effect
Mccoid: Hmm Rumery it is just, when you have many things like SUM and LENGTH and similar, and then you need it in the SELECT and the WHERE, lots of duplicated code
Rumery: Djam90, yes, but to be able to use the alias in where you would need to wrap it all in another query, thats usually even worse for readability. but that is for “normal” expressions – for sum you cannot use WHERE anyway, thats what HAVING is for
Bawden: Each user have friends also from users table
Jorski: Users have column last_seen_at
Barre: If last_seen_at is 1 minute ago, user is online
Rahall: Now I want to get list of user friends, which should begin with friends online, sorted by name
Hautamaki: And after firends online the rest friends, also sorted by name
Saine: I can do this in one query with UNION, but I have problem with sorting results by name, because ORDER BY doesn’t work with UNION
Gardenas: Is it possible to do this in one query?
Clyatt: Toomus: ORDER BY works with UNION, but you don’t need UNION. You need join
Rumery: Toomus, you want to order by “name”, but only after ordering by “online”
Spinks: Rumery: you mean ORDER BY two columns? First last_seen_at DESC, and then name ASC?
Demiel: I’m having a tough time getting my mysql server to start
Demiel: I’ve tried increasing innodb_force_recovery to 4, but it still won’t start
Rumery: Toomus, using simply “last_seen_at” would order everything by that time and only those with the same time by name – but you can order by your expression to “decide” who is online – boolean expressions return 0/1 as false/true so can be ordered by
Prince: Rumery: Yeah, this doesn’t work http://pastie.org/10418650
Rumery: Toomus, yes, because it orders them ALL by the “last click”
Rumery: Toomus, how do you compute the “is online” state? in sql or in app code?
Rumery: For this to work you need to compute that in sql
Rumery: Toomus, and as salle wrote, you probably want to add a join between user and friends
Moranda: Rumery: list of friends is holded in user column friend_ids as serialized array
Rumery: Toomus, ah : can you change that to a separate table?
Biase: Rumery: I can’t : But this solution is pretty fast
Rumery: But it is not a problem for the ordering itself, only a thing which should be generally avoided
Dicello: Rumery: I’m using Ruby on Rails framework
Halfhill: Rumery: So it is easy to write/read serialized array from DB
Rumery: Toomus, RoR has nothing to do with that design unless it is THAT bad
Arcea: Toomus: http://gtowey.blogspot.com/2009/12/how-to-fix-comma-separated-list-of-doom.html
Rumery: Toomus, serialized array is only a different version of this
Rumery: Toomus, imagine facebook with millions of users and average hundred of friends per user – just searching them your way would be painfully slow
Rumery: Serialized array has a few drawbacks – cannot be indexed to search fast, no easy way to do a query “did this person already befriended that one” etc
Rumery: But there are cases when you just cannot change that or not at the moment, it happens. so unless you can, we should return to the ordering problem : just keep these for later