0

How can i force a type in db:select(db::raw($query)) on PHP??

I have a really complex query that I'm getting data from, to do so I'm using DB::raw($query).

I'm using the postgreSQL array_agg() function in 2 columns.

SQL example:

$query =   'select
              a,
              array_agg(b) FILTER (where b is not null) as b,
              array_agg(c) FILTER (where c is not null) as c
            from table X 
            group_by a'

The result of the SQL using PGAdmin is:

-----------------------
|A  |    B    |  C    |
|int|   int[] | int[] |
|---------------------|
|1  | {1,2,3} | null  |
|2  | {5,6,7} | null  |
|3  |  null   |  {8}  |
|4  |  null   |{9,10} |
-----------------------

That said when I do:

$value = DB::SELECT(DB::raw($query))

As expected $value is an array of objects.

That said the properties b and c are not arrays of ints, instead they are strings.

dd($value) looks like:

array:4[
0 =>{
 "a":1
 "b":"{1,2,3}"
 "c":null
}
1 =>{
 "a":2
 "b":"{4,5,6}"
 "c":null
}
2 =>{
 "a":3
 "b":null
 "c":"{8}"
}
3 =>{
 "a":4
 "b":null
 "c":'{9,10}'
}
]

Is there a way to get the values of "b" and "c" as an array or as null??

There are to many elements in the array ($value) to be viable to loop through it to do the cast.

OBS: I'm using php7.4.9

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Felipe Colussi-oliva
  • 168
  • 1
  • 8

1 Answers1

0

A way would be to use the transform method over your results and convert the results as per your needs

$values = $values->transform(function ($item) {

    if (! is_null($item->b)) {
        $item->b = array_map(
            'intval',
            explode(
                ',',
                str_replace(
                    ['{', '}'], '', $item->b
                )
            )
        );
    }

    if (! is_null($item->c)) {
        $item->c = array_map(
            'intval',
            explode(
                ',',
                str_replace(
                    ['{', '}'], '', $item->c
                )
            )
        );
    }

    return $item;

});

If you are looking for something at the Database Level, Please check out this thread and this answer

linktoahref
  • 7,812
  • 3
  • 29
  • 51
  • Thanks for helping me to improve tue question!! The Database is working as intended, if I do the query on another language (golang) it maps the response to a slice (array) of int. The transform method will iterate over all the original list (10.000~50.000 itens), i'm trying to avoid that. OBS: Today i'm returning it as a string and i'm leting the "consumer" to split it up to avoid and extra iteration. OBS2: I have no idea how bad an extra iteration would be. I can be over complicating it. – Felipe Colussi-oliva Feb 15 '21 at 13:35
  • Your Question was improved not by me but by marc_s, An option would be to use pagination instead of returning a large data set. – linktoahref Feb 15 '21 at 15:13