Syntax To Extract An Array of One Field Value From a Database Query

Wappler General How To
#1

I have a database query invoices which returns lots of fields - id, activity_id, description etc:

id    activity_id   description
==================================
123       456        description1
321       654        description2
...

I want to use an array of activity_id as the input to an IN clause in another query.
For this to work I need to return an array, not a comma separated list.

So for the example above, the array contains 456 and 654.

What syntax do I use to create the array?

#2

Iā€™ve just worked out I can do it like this:

invoices.join(",", "activity_id").split(',')

Is there any other way which seems more elegant? :thinking:

#3

Have you tried group by & keys?
I donā€™t remember the exact syntax, but it should look something like this:
query1.groupBy('activity_id').keys().

1 Like
#4

Not sure what do you call more elegant, but in order to split a comma separated string values into an array you need to use split, so this is the correct way of doing it.
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/split

#5

For an IN condition, you can use the unique() formatter as whether something appears more than once is irrelevant.

1 Like
#6

Hi there Teodorā€¦

In my example, I am converting a keyed array (the invoices query) into a comma separated string and then into a simple array.

I was wondering if I can just extract the simple array from the query resultsā€¦ so to go straight to [456], [654] rather than via "456,654"

Is there a syntax which does this, so something like:

invoices.SOME_FUNCTION('activity_id')
#7

Iā€™d say invoices.unique('activity_id') constitutes cleaner/more elegantā€¦ :beers:

#8

unique wonā€™t return the values in an array like itā€™s required by Antony.

"unique": {
"0": "val1",
"1": "val2",
"2": "val3"
}

"split": [
"val1",
"val2",
"val3"
]

Split is the way to go here.

#9

Genius, I didnā€™t know you could do that, with a parameter for unique().

Works a treat!

So how would you do it if your situation didnā€™t require them to be unique I wonder? :thinking:

1 Like
#10

For an IN condition, unique will always be fine. I did request a version that returned non-unique values here:

Maybe a proper feature request is required.

1 Like
#11

Actually Teodor, right now it does! And the result works in the IN statementā€¦

split

Is this by accident or by design? I donā€™t want to use a feature that then disappears!

#12

Well thatā€™s how it is supposed to work. I was just testing with strings, which outputs a bit different different result.

1 Like
#13

Go for it!

Thanks for all your help Benā€¦ I now see you were talking about this only a couple of weeks ago!

1 Like
#14

BREAKING NEWS - BREAKING NEWS - BREAKING NEWS - BREAKING NEWS

@Teodor, you are absolutely right to say that .split() is the way to go.

The reason is that .unique() can end up leaving you with arrays which have a non continuous index:

offset

And this causes an ā€œOffsetā€ error in the MySQL which uses that array in the IN statement.

There is a fix for thisā€¦

You need to join, split, make the array unique and then join and split it again!

invoices.join(",", "activity_id").split(',').unique().join(',').split(',')
1 Like
#15

Sounds like a custom formatter might be the best step forward on this, for now. Feature request in:

#16

I donā€™t see whatā€™s the point of dealing with custom coding / formatters when you can just get your comma separated string value and split it, as itā€™s already available?

#17

@teodor, it just seemed strange to go to a flat textual list and then back to an array.

However the offset issue has shown that join/split is the way to go!

#18

:partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face:
THANK YOU FOR ALL YOUR HELP EVERYONE!
:partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face:

#19

@Antony did this not work?

#20

If you are interested in a formatter (personally, I prefer a single one to chaining a few) to get either the unique values (with sequential keys) or all values for a given property, hereā€™s a formatter for each (PHP) - They are just a slight tweak on the unique formatter that already exists:

function formatter_uniquearray($val, $prop = NULL) {
    if (!is_array($val)) return array($val);
    return array_values(array_unique($prop != NULL ? array_column($val, strval($prop)) : $val));
}
function formatter_columnarray($val, $prop = NULL) {
    if (!is_array($val)) return array($val);
    return array_column($val, strval($prop));
}

uniquearray() is the same as unique, but using the array_values function to return the values without non-consecutive index.

1 Like