Rikiavimas

Grzegorz Brzęczyszczykiewicz, 6th Mar 2021 6:51 pm GMT+0200 permalink

Yra tokia nedidelė savadarbė DVS.
Joje kiekvienas dokumentas turi ID ir 'follows_id', kuris rodo po kurio
dokumento turi eiti šis dokumentas. Dokumentų daug, bet dokumentų
rinkinio pirmo dokumento 'follows_id'==0.

Mano bėda ta, kad ID nedidėja monotoniškai ir įvedus naują dokumentą jis
gali atsidurti labai pradžioje. Kol kas nenorių įvesti 'sortorder' kaip
atskiro lauko, manau kad 'linked list' turi šiokių tokių privalumų.

Kaip protingai išrikiuoti dokumentus pagal jų loginę seką?
PHP arba MySQL.

https://stackoverflow.com/questions/29655419/mysql-order-by-fields-parents
nepadeda, nes ID nedidėja monotoniškai, t.y. pagal šią nuorodą
'sortorder' gaunu:

SELECT id, project_id, follows_id, CONCAT(CAST(follows_id AS CHAR),'-',
CAST(id AS CHAR)) AS sortorder FROM documents where project_id=25
ORDER BY sortorder

id project_id follows_id sortorder
280 25 0 0-280
267 25 276 276-267
268 25 267 267-268
281 25 280 280-281
282 25 281 281-282
283 25 282 282-283
285 25 284 284-285
276 25 285 285-276

O man reikia:
id project_id follows_id sortorder
280 25 0 0-280
281 25 280 280-281
282 25 281 281-282
283 25 282 282-283
284 25 283 283-284
285 25 284 284-285
276 25 285 285-276
267 25 276 276-267
268 25 267 267-268

--
Grzegorz

Chrząszczyrzewoszyce, powiat Łękołody

Grzegorz Brzęczyszczykiewicz, 6th Mar 2021 11:21 pm GMT+0200 permalink

2021-03-06 18:51, Grzegorz Brzęczyszczykiewicz rašė:

Dabar problemą sprendžia štai toks pargiuvęs PHP /Laravel kodas, bet
kažkodėl norwčiau užkrauti tą darbą SQL'ui:

$tmp_documents = �App�Document::where('project_id',
$project->id)->orderBy('follows_id', 'asc')->get();

$documents = collect([$tmp_documents->first()]);
$prev_document = $tmp_documents->first()->id;
$tmp = $tmp_documents->slice(1);

while (!$tmp->isEmpty()) {
$doc = $tmp->where('follows_id',$prev_document)->first();
```
$prev_document = $doc->id;
$documents->push($doc);

$tmp = $tmp->diff([$doc]);
```
}

--
Grzegorz

Chrząszczyrzewoszyce, powiat Łękołody
krk, 7th Mar 2021 4:11 pm GMT+0000 permalink

Gal cia kazkas panasaus?

https://stackoverflow.com/questions/8022158/how-do-i-order-by-parent-then-child

Grzegorz Brzęczyszczykiewicz Grzegorz@Brzeczyszczykiewicz.tudai wrote:
>

Yra tokia nedidelė savadarbė DVS.
Joje kiekvienas dokumentas turi ID ir 'follows_id', kuris rodo po kurio
dokumento turi eiti šis dokumentas. Dokumentų daug, bet dokumentų
rinkinio pirmo dokumento 'follows_id'==0.

Mano bėda ta, kad ID nedidėja monotoniškai ir įvedus naują dokumentą jis
gali atsidurti labai pradžioje. Kol kas nenorių įvesti 'sortorder' kaip
atskiro lauko, manau kad 'linked list' turi šiokių tokių privalumų.

Kaip protingai išrikiuoti dokumentus pagal jų loginę seką?
PHP arba MySQL.

https://stackoverflow.com/questions/29655419/mysql-order-by-fields-parents
nepadeda, nes ID nedidėja monotoniškai, t.y. pagal šią nuorodą
'sortorder' gaunu:

SELECT id, project_id, follows_id, CONCAT(CAST(follows_id AS CHAR),'-',
CAST(id AS CHAR)) AS sortorder FROM documents where project_id=25
ORDER BY sortorder

id project_id follows_id sortorder
280 25 0 0-280
267 25 276 276-267
268 25 267 267-268
281 25 280 280-281
282 25 281 281-282
283 25 282 282-283
285 25 284 284-285
276 25 285 285-276

O man reikia:
id project_id follows_id sortorder
280 25 0 0-280
281 25 280 280-281
282 25 281 281-282
283 25 282 282-283
284 25 283 283-284
285 25 284 284-285
276 25 285 285-276
267 25 276 276-267
268 25 267 267-268
- Grzegorz Brzęczyszczykiewicz, 7th Mar 2021 8:33 pm GMT+0200 permalink
  
  2021-03-07 18:11, krk rašė:
  
  Gal cia kazkas panasaus?
  
  https://stackoverflow.com/questions/8022158/how-do-i-order-by-parent-then-child
  
  t.y. padaugint iš didelio skaičiaus ...
  Reiktų patikrinti, bet bendru atveju 9987 gali eiti prieš 22.
  
  Pasidariau 'stack trace'. Kol kas pigiai surikiuot nesigaus.
  
  0: found ID 281: 280, now searching for 281, remaining length 199
  1: found ID 282: 281, now searching for 282, remaining length 198
  2: found ID 283: 282, now searching for 283, remaining length 197
  3: found ID 284: 283, now searching for 284, remaining length 196
  4: found ID 285: 284, now searching for 285, remaining length 195
  5: found ID 286: 285, now searching for 286, remaining length 194
  6: found ID 276: 286, now searching for 276, remaining length 193
  7: found ID 277: 276, now searching for 277, remaining length 192
  8: found ID 278: 277, now searching for 278, remaining length 191
  9: found ID 253: 278, now searching for 253, remaining length 190
  10: found ID 254: 253, now searching for 25, remaining length 189
  11: found ID 255: 254, now searching for 255, remaining length 188
  12: found ID 288: 255, now searching for 288, remaining length 187
  13: found ID 256: 288, now searching for 256, remaining length 186
  14: found ID 257: 256, now searching for 257, remaining length 185
  ...
  101: found ID 363: 326, now searching for 363, remaining length 98
  102: found ID 359: 363, now searching for 359, remaining length 97
  103: found ID 358: 359, now searching for 358, remaining length 96
  104: found ID 381: 358, now searching for 381, remaining length 95
  105: found ID 357: 381, now searching for 357, remaining length 94
  106: found ID 380: 357, now searching for 380, remaining length 93
  107: found ID 356: 380, now searching for 356, remaining length 92
  108: found ID 379: 356, now searching for 379, remaining length 91
  109: found ID 355: 379, now searching for 355, remaining length 90
  110: found ID 378: 355, now searching for 378, remaining length 89
  111: found ID 354: 378, now searching for 354, remaining length 88
  112: found ID 377: 354, now searching for 377, remaining length 87
  ...
  186: found ID 414: 415, now searching for 414, remaining length 13
  187: found ID 413: 414, now searching for 413, remaining length 12
  188: found ID 412: 413, now searching for 412, remaining length 11
  189: found ID 411: 412, now searching for 411, remaining length 10
  190: found ID 410: 411, now searching for 410, remaining length 9
  191: found ID 409: 410, now searching for 409, remaining length 8
  192: found ID 438: 409, now searching for 438, remaining length 7
  193: found ID 475: 438, now searching for 475, remaining length 6
  194: found ID 320: 475, now searching for 320, remaining length 5
  195: found ID 474: 320, now searching for 474, remaining length 4
  196: found ID 473: 474, now searching for 473, remaining length 3
  197: found ID 425: 473, now searching for 425, remaining length 2
  
  --
  Grzegorz
  
  Chrząszczyrzewoszyce, powiat Łękołody
Laimis, 8th Mar 2021 12:14 am GMT+0200 permalink

Grzegorz Brzęczyszczykiewicz rašė:

Yra tokia nedidelė savadarbė DVS.

SQLite, nors CTE palaikymą MySQL'as tikrai turi, tad turėtų veikti ir
ten (galbūt minimaliai pasikoregavus):

WITH RECURSIVE cte(id,project_id,follows_id) AS (
SELECT id, project_id, follows_id FROM documents WHERE follows_id=0
UNION ALL
SELECT t.id, t.project_id, t.follows_id FROM documents t
JOIN cte ON cte.id = t.follows_id
) SELECT * FROM cte;
- Attachments:
- signature.asc (834 Byte)
- Grzegorz Brzęczyszczykiewicz, 8th Mar 2021 4:29 pm GMT+0200 permalink
  
  2021-03-08 00:14, Laimis rašė:
  
  Grzegorz Brzęczyszczykiewicz rašė:
  
  Yra tokia nedidelė savadarbė DVS.
  
  SQLite, nors CTE palaikymą MySQL'as tikrai turi, tad turėtų veikti ir
  ten (galbūt minimaliai pasikoregavus):
  
  Ačiū,
  
  pasirodo Laravel tiesiogai nepalaiko CTE, belieka 'raw query' arba
  prisukti https://github.com/staudenmeir/laravel-cte
  
  --
  Grzegorz
  
  Chrząszczyrzewoszyce, powiat Łękołody

A short format overview