cinci zoo sniper posted:

c tp mysql s:

SQL code:

SELECT
  foo.a,
  foo.b,
  foo.c,
  GROUP_CONCAT(bar.b) AS X
FROM table foo
  LEFT JOIN (SELECT
               baz.a,
               baz.b,
               baz.c
             FROM table baz) bar ON foo.a = bar.a AND foo.c > bar.c

yields, figuratively speaking X1 X2 X1 X2 X1 X2 X1 X2 X1 X2 as the output of single X. substituting for inner join gives me X1 X1 X1 X1 X1 X2 X2 X2 X2 X2

gonadic io posted:

lol nice. would be better with "terrible programmers:" at the front though

St Evan Echoes posted:

you don't truly become a terrible programmer until you know just enough to really gently caress yourself and your colleagues

St Evan Echoes posted:

what do you want the result to be

gonadic io posted:

this is the thing, newbie programmers aren't terrible programmers. they're bad, but they're bad and ineffectual. it's the subtle bugs that kill you not the glaring syntax errors

gonadic io posted:

It's like president trump vs president pence

cinci zoo sniper posted:

let me rewrite the query better first

SQL code:

SELECT
  foo.a,
  foo.b,
  foo.c,
  GROUP_CONCAT(bar.a) AS bar_a
FROM table foo
  LEFT JOIN (SELECT
               baz.a,
               baz.b,
               baz.c
             FROM table baz) bar ON foo.b = bar.b AND foo.c > bar.c
WHERE foo.c BETWEEN '2016-01-01' AND '2016-12-13'

the data itself has a couple more years prior to 2016

what i want to do is to retrieve foo.* for every 2016 record, and every historical foo.a (which is incremental) that matches the group id number foo.b, as a string.

the problem im running in at the moment is oddball scenarios where i get loads of duplicates (for example, if i replace WHERE clause with "WHERE foo.a = 21969420" that matches some late 2016).

i can fix this by slamming DISTINCT into GROUP_CONCAT, or by doing some seemingly painful (this query humongous irl) refactoring so that the innermost loop contains all foos, which seems to invoke a lot of problems related to filtering and ranking. im reluctant to do the latter, and i wonder if there is something faster than solution with DISTINCT, which appears to be significantly slowing down the already terrible query (100k target foo rows with joins to a couple dozen tables, lots of GROUP_CONCATS and general querying without existing indices)

also i would like to understand what is going off in the first place, since the DISTINCT option sounds like a bandaid, and it really shouldnt be spitting out 10 copies of bar.a, each with all 5 baz.a in it (for that WHERE substition example). my best guess was that something related to joining a value on an array, but COALESCE(bar.b) in ON clause did not change anything

the understand will help to fix other dozen of sub queries with the exact same problem

Symbolic Butt posted:

javascript is losing some steam in the language popularity contest, it may become the new ruby I think

St Evan Echoes posted:

hmm i got lost like a sentence and half into your post, just throw away the whole query and start again imo

cinci zoo sniper posted:

will see what i can do i guess. tl;dr on my post was that i want to look up same table entries preceding the scope i'm querying, and i keep getting duplicates without hacky solutions

hobbesmaster posted:

developer holding a screw driver and jtag cable: “this is a lot easier than fixing the JavaScript”

St Evan Echoes posted:

what happens if you put

code:

GROUP BY foo.a, foo.b, foo.c

at the end of that query

hobbesmaster posted:

developer holding a screw driver and jtag cable: “this is a lot easier than fixing the JavaScript”

akadajet posted:

not as long as it's the default language for web browsers.

cinci zoo sniper posted:

will see what i can do i guess. tl;dr on my post was that i want to look up same table entries preceding the scope i'm querying, and i keep getting duplicates without hacky solutions

Powerful Two-Hander posted:

no wait can't you do this instead?

code:

Select
Foo. *, 
(
Select
Baz. A
From baz
Where
Baz.b =foo. B
And Baz.c <foo. C
) as bar
From 
Foo 
Where
Foo.c between (...) 

i guess you can concat bar? If this was mssql I'd stick a for xml path('') on there to get the concatenation because there's no built in function for it yet but I assume group_concat will do that for you...

gonadic io posted:

this is the thing, newbie programmers aren't terrible programmers. they're bad, but they're bad and ineffectual. it's the subtle bugs that kill you not the glaring syntax errors

a witch posted:

has anyone here used D? do you have opinions on D?

i wanna write a network daemon thingy and I’m looking at D idk

St Evan Echoes posted:

giggled like a child at this entire post

cinci zoo sniper posted:

this but unsuccesfully tried to make an adequate joke about it

Captain Foo posted:

we know your D is an inadequate joke

cinci zoo sniper posted:

gdi, i walked right into this, didnt i

Zemyla posted:

From what I heard, Rust sucked away pretty much all consumers of D.

gonadic io posted:

cinci zoo sniper posted:

a witch posted:

what if I love rust but hate tokio?

HoboMan posted:

is there a reason you are using a subquery instead of a normal join?

also you can just use CONCAT() i think
e: like

SQL code:

SELECT
  foo.a,
  foo.b,
  foo.c,
  CONCAT(baz.a) AS bar_a
FROM foo
  LEFT JOIN baz ON foo.b = baz.b AND foo.c > baz.c
WHERE foo.c BETWEEN '2016-01-01' AND '2016-12-31'

not actually helping you on your question because i still don't understand it, but simplifying syntax can be a good first step to wrapping your head around it

cinci zoo sniper posted:

what i want to do is to retrieve foo.* for every 2016 record, and every historical foo.a (which is incremental) that matches the group id number foo.b, as a string.

the problem im running in at the moment is oddball scenarios where i get loads of duplicates (for example, if i replace WHERE clause with "WHERE foo.a = 21969420" that matches some late 2016).

i can fix this by slamming DISTINCT into GROUP_CONCAT, or by doing some seemingly painful (this query humongous irl) refactoring so that the innermost loop contains all foos, which seems to invoke a lot of problems related to filtering and ranking. im reluctant to do the latter, and i wonder if there is something faster than solution with DISTINCT, which appears to be significantly slowing down the already terrible query (100k target foo rows with joins to a couple dozen tables, lots of GROUP_CONCATS and general querying without existing indices)

also i would like to understand what is going off in the first place, since the DISTINCT option sounds like a bandaid, and it really shouldnt be spitting out 10 copies of bar.a, each with all 5 baz.a in it (for that WHERE substition example). my best guess was that something related to joining a value on an array, but COALESCE(bar.b) in ON clause did not change anything

the understand will help to fix other dozen of sub queries with the exact same problem

redleader posted:

yeah, you're right. i misspoke; i said 'overengineered' when i actually meant 'verbose and boilerplatey'

gonadic io posted:

YOSPOS > i actually have reasons to post here unlike half of the people with impostor syndrome

lol nice. would be better with "terrible programmers:" at the front though

e:


now i need to work out how to draw a border. i want the line to be inside the hex, so it can't be specified using the same coords as the hex verts i think

e2: also i'm pretty sure this framework doesn't support selection mode so i have to work out which hex gets clicked on directly from the mouse pos hurray. luckily the hex resource i posted about earlier tells you the math to do this

HoboMan posted:

if more than one foo can have the same group id then you are going to have duplicates. (period)

the solution is to use GROUP_CONCAT(DISTINCT foo.whatever), it's not hacky

the reason COALESCE does nothing is because comparing null to anything (including null) is always false


it is slow because you are concatenating 100k rows into a goddamn string wtf are you doing!?!?!

quote:

if more than one foo can have the same group id then you are going to have duplicates. (period)