Inserting Raw SQL

[riseoflex88]

        // Destination ID's
        $destinations = [75, 97, 103];

        $fields = [];
        foreach ($destinations as $destination_id) {
            $fields[] = field('destination_id')->eq($destination_id);
        }

        // This works, but it's assuming there are always 3 elements
        $this->sql_ob->andWhere(group(
            $fields[0]
            ->or($fields[1])
            ->or($fields[2])
        ));

I need a function / method that can take the array of $queries, and add them into the andWhere statement. The problem is that the '->or' method is required on the subsequent elements.

If that's not possible, is there a method to add Raw SQL into a where statement?

[riseoflex88]

It feels like this is still just as much a general php question as it is a Latitude question, I'll keep hunting for an answer myself but if anyone can put me out of my misery it'd be greatly appreciated.

[riseoflex88]

Yay, got it...

Code fragment removed by @shadowhand because it is was vulnerable to SQL Injection.

Criteria is the key, make it in raw sql, then use criteria to interpret that into the Latitude format

Thanks again @shadowhand for a great package!

[shadowhand]

This seems like a situation where IN would be appropriate:

$query->andWhere(field('pd.destination_id')->in($destinations));

[shadowhand]

@riseoflex88 definitely do not use the code you posted, see my edit above.

[riseoflex88]

The code you suggested is where I started but didn't work for me, I think I must have overlooked the error's I was experiencing so tried a different route.

$query->andWhere(field('pd.destination_id')->in($destinations));

I'm not sure this is building the correct output when providing an array. If I provide an array as above, I get an "Array to string conversion" error and the SQL produced looks like:

WHERE pd.destination_id IN (Array)

However, if I convert to a string...

$query->andWhere(field('pd.destination_id')->in(implode(', ', $destinations)));

SQL Output:

WHERE pd.destination_id IN ("a, b, c")

It'll (obviously) wrap the string in quotes so the query will look for a destination id of "a, b, c"

I've tried looking through the latitude code for a suggestion on what needs to change, but I can't find one,

[shadowhand]

Ah, my mistake. I forgot to unpack:

$query->andWhere(field('pd.destination_id')->in(/* unpack change */ ...$destinations));

Let me know if that works.

[riseoflex88]

Ah, perfect. Sorry that's me not understanding the use of ... to even make a guess at adding that in there