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

Wappler General Need Help
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.

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