Thread replies: 15
Thread images: 5
Thread images: 5
File: 1493868771087.jpg (114KB, 1280x720px) Image search:
[Google]
114KB, 1280x720px
Help me solve this.
So, I need to make an MySQL query of one table containing the `index`, data `id`, data id `parent`, when the data was `inserted_on`, and when it was `updated_on`. The table is self-contained, both <parent> and <children> is determined by it's `parent` value, and the `updated_on` value is determined by the last record of a <children> it has. And the result must have an index to sort the data based on the `updated_on` value.
In short, updated_on = children_count > 0 ? last_children_inserted_on : self_inserted_on.
>Sample table:
[ id | parent | inserted_on | data | updated_on ],
[ 1 | 0 | 2017-05-04 09:29:37 | data | ],
[ 2 | 1 | 2017-05-04 16:00:45 | data | ],
[ 3 | 0 | 2017-05-04 16:01:13 | data | ],
[ 4 | 1 | 2017-05-04 16:28:09 | data | ],
[ 5 | 0 | 2017-05-04 16:28:11 | data | ],
[ 6 | 1 | 2017-05-04 16:28:18 | data | ],
[ 7 | 5 | 2017-05-04 16:28:23 | data | ],
[ 8 | 1 | 2017-05-04 16:28:32 | data | ],
[ 9 | 3 | 2017-05-04 16:28:39 | data | ],
[ 10 | 0 | 2017-05-04 16:28:41 | data | ]
>Result I expect
[ index | id | parent | inserted_on | data | updated_on DESC ],
[ 1 | 10 | 0 | 2017-05-04 16:28:41 | data | 2017-05-04 16:28:41 ],
[ 2 | 3 | 0 | 2017-05-04 16:01:13 | data | 2017-05-04 16:28:39 ],
[ 3 | 1 | 0 | 2017-05-04 09:29:37 | data | 2017-05-04 16:28:32 ],
[ 4 | 5 | 0 | 2017-05-04 16:28:11 | data | 2017-05-04 16:28:23 ]
>>
>>309643
>Query (so far):
SELECT
@i := @i + 1 AS `index`,
`datas`.`id`,
`datas`.`parent`,
`datas`.`inserted_on`,
`timestamps`.`updated_on`
FROM
`datas`,
(SELECT @i := 0) AS `iterator`,
(SELECT `id` AS `parent`, MAX(`inserted_on`) AS `updated_on` FROM (SELECT `id` AS `id`, `inserted_on` FROM `posts` UNION ALL SELECT `parent` AS `id`, `inserted_on` FROM `datas`) AS `t` GROUP BY `parent`) AS `timestamps`
WHERE
`posts`.`parent` = '0' AND `timestamps`.`parent` = `posts`.`id`
ORDER BY `timestamps`.`updated_on` DESC
>I get:
[ index | id | parent | inserted_on | data | updated_on DESC ],
[ 4 | 10 | 0 | 2017-05-04 16:28:41 | data | 2017-05-04 16:28:41 ],
[ 2 | 3 | 0 | 2017-05-04 16:01:13 | data | 2017-05-04 16:28:39 ],
[ 1 | 1 | 0 | 2017-05-04 09:29:37 | data | 2017-05-04 16:28:32 ],
[ 3 | 5 | 0 | 2017-05-04 16:28:11 | data | 2017-05-04 16:28:23 ]
Why does the `index` got jumbled? Where did I get it wrong?
Is there better way to achieve result I seek?
>>
>>309643
Use a subquery.
You're adding your index* before ORDER BY orders them.
* please don't call it that: indices are already a thing in SQL.
>>
>>309643
Also you know GROUP BY is a thing, right?
>>
File: same result.jpg (7KB, 103x339px) Image search:
[Google]
7KB, 103x339px
>>309648
>>309651
Same result. I've been pulling my hair all night at this.
>GROUP BY
I might suck at this, but I'm not an utter idiot.
>>
>>309654
>I'm not an utter idiot
You're using WHERE x=y instead of JOIN, so you see why I had to ask.
>>
File: kill me.jpg (15KB, 297x150px) Image search:
[Google]
15KB, 297x150px
>>309658
I tried that also. For some reason, MySQL thinks the left table is non-existent.
>>
>>309654
I'm really failing to see how your problem can't be solved by joining children onto parents, then taking the MAX of modified time.
It looks like a classic use for GROUP BY, and I'm not seeing where the problem is.
>>
File: Facepalm_anime.jpg (40KB, 500x336px) Image search:
[Google]
40KB, 500x336px
>>309662
Solved it. I admit I was a fool.
Much obliged, Anon.
>>
why not have the parent column be null for parent (instead of zero)? then do a left join on it and ditch the 'index'/'iterator' thing-ma-bob (if you want to order by date you can do so in either the result set or on the consumer of the data)
lemme make a schema real quick, brb
>>
>>
>>309676
mmm, turns out i gotta use a union, left join would lead to either "incorrect" (more) results or i would need to nest another select (not sure which is faster on such low data, union all is probably a bit slower than an inner select but avoids code/query replication)
its not much different from what you already had
>schema
https://pastebin.com/ntf1xbLT
>query
https://pastebin.com/tLs3Pf6t
the code is pretty self explanatory, the first part selects only "the ones with children" the other part the ones with no children (group/filtering is done in place so nodes with children are/should preffered/kept over those without)
and MODS, we could really use a code tag around here...
>>
in fact now that i look a bit closer at >>309644 (after some format), >>309704 is for the most part the same thing
kek, i need some sleep
Thread posts: 15
Thread images: 5
Thread images: 5