\IPS\Db::i()->in

[HeadStand]

@Mark I'm wondering if the implementation of this can be revisited.

I've found that when using this method in combination with other where conditions, your where clause breaks - unless the IN clause is placed LAST.

For example, if I do something like this:

$where[] = \IPS\Db::i()->in( 'category_id', $categories );
$where[] = array( 'member_id=?', $memberId );

The IN is included but the remaining clauses are ignored. However, if I simply reverse the order

$where[] = array( 'member_id=?', $memberId );
$where[] = \IPS\Db::i()->in( 'category_id', $categories );

That works correctly.

Most of the time you can rearrange the code to make this work. However, I'm currently using \IPS\Helpers\Table\Db, and one of my clauses uses \IPS\Db::i()->in. The problem occurs when I use the advancedSearchForm. Because the filtering is always added to the where clause last, my table breaks.

For now I'm going to replace \IPS\Db::i()->in with something like

$where[] = array( 'category_id in (?)', implode( ",", $categories ) );

But I still think this could maybe use some review.

Thoughts?

[newbie LAC]

7 hours ago, HeadStand said:

For example, if I do something like this:

Change to

$where[] = array( \IPS\Db::i()->in( 'category_id', $categories ) );
$where[] = array( 'member_id=?', $memberId );

 

[bfarber]

Yes, you need to put the result into an array as suggested above.

[Mark]

FWIW though, improving where clauses is on my list

[HeadStand]

3 hours ago, bfarber said:

Yes, you need to put the result into an array as suggested above.

I have absolutely no idea why I did not attempt that. No excuses. SHAME ESTHER, SHAME.

[Midnight Modding]

I'm not able to get IN() to work at all in Table\Db, whether using IN(?) or \IPS\Db::i()->in(). It keeps only returning one row if my $in is '1,2,3', yet if I use field=1 or field=2 or field=3, it returns 3 rows.

[Mark]

12 minutes ago, Midnight Modding said:

I'm not able to get IN() to work at all in Table\Db, whether using IN(?) or \IPS\Db::i()->in(). It keeps only returning one row if my $in is '1,2,3', yet if I use field=1 or field=2 or field=3, it returns 3 rows.

Show us the code.

[Midnight Modding]

2 minutes ago, Mark said:

Show us the code.

			$table = new \IPS\Helpers\Table\Db( 'tableOne', $url, array( array( \IPS\Db::i()->in( 'test_id', $x['ids'] ) ) ) );
// that doesn't work

			$teamTable = new \IPS\Helpers\Table\Db( 'tableOne', $url, array( array( 'test_id IN(?)', $x['ids'] ) ) );
// that doesn't work


			$teamTable = new \IPS\Helpers\Table\Db( 'tableOne', $url, array( array( 'test_id=? or test_id=? or test_id=?', 1, 2, 3 ) ) );
//does work

I even dumped $x['ids'] to confirm it indeed is '1,2,3'.

[newbie LAC]

When you will read docs/code?

16 minutes ago, Midnight Modding said:

I'm not able to get IN() to work at all in Table\Db, whether using \IPS\Db::i()->in(). It keeps only returning one row if my $in is '1,2,3', yet if I use field=1 or field=2 or field=3, it returns 3 rows.

 

	/**
	 * IN
	 * Generates a WHERE clause to determine if the value of a column matches any value from an array
	 *
	 * @param	string	$column			Column name
	 * @param	array	$values			Acceptable values
	 * @param	bool	$reverse		If true, will match cases where $column does NOT match $values
	 * @return 	string	Where clause
	 * @see		\IPS\Db::findInSet()	For columns that contain comma-delimited lists
	 */
	public function in( $column, $values, $reverse=FALSE )

     * @param    array    $values            Acceptable values

Array

Your var value is string

---

18 minutes ago, Midnight Modding said:

I'm not able to get IN() to work at all in Table\Db, whether using IN(?). It keeps only returning one row if my $in is '1,2,3', yet if I use field=1 or field=2 or field=3, it returns 3 rows.

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_in

Quote

mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

 

 

[Midnight Modding]

edit: sigh it does work with an array in this case. For IN(?) it didn't work either way, though, and in various places in the suite I saw it using IN both of these ways so I assumed you could do it interchangeably, but apparently there is something in the table helper that has IN(?) not work.

Also, I did look at the doc, but overlooked that it had to be an array. It's easy to overlook it because it's only in the one place on that one, whereas I usually am reading the descriptions instead.

Also I'm not sure what you're trying to tell me on that example. Mine was done just like this one from ip.suite:

			\IPS\Db::i()->delete( 'core_theme_settings_values', array( 'sv_id IN(?)', implode( ',', $valueIds ) ) );

it obviously worked there by having the column name and using a comma separated list of ids in IN() and that's how I always do it. It's just not working in the table class's parameter.

So it works with the IPS in() when using an array, but that still does not explain why I can't do it with the mysql IN() like above. My example did not have different types... I used implode() on an array and did it exactly like the above example I gave from the first party file. If I was using different types, it never would have returned any result at all... so I'm jjust not sure what you're saying there.

And, by the way, I don't think these 3 rows are even supposed to be in the db... yet another issue.

[Midnight Modding]

I've also used 'something IN(?)', \IPS\Db::i()->select( ... ) before. I see IPS using it that way, quite a bit. I just don't get why my second "that doesn't work" example above has a problem. If it works elsewhere, such as in delete(), why would the where condition be handled differently in Table\Db? I obviously don't want to sue IN(?) in other areas and run into the same issue. Really, using the IPS method with array is kind of easier... but just like knowing what the issue was.

[newbie LAC]

1 hour ago, Midnight Modding said:

Also I'm not sure what you're trying to tell me on that example. Mine was done just like this one from ip.suite:

			\IPS\Db::i()->delete( 'core_theme_settings_values', array( 'sv_id IN(?)', implode( ',', $valueIds ) ) );

 

It's a bug

[Midnight Modding]

2 hours ago, newbie LAC said:

It's a bug

So we aren't supposed to use the comma separated lists in IN(?) in 4.x, either? Is IN(?) replaced automaticallywith \IPS\Db::i()->in()? I just assumed IN(?) was directly using the mysql IN(). (also, I remember I tried both '1,2,3' and array( 1,2,3 ) with IN(?) and neither worked... Or maybe you meant the bug is in the table helper? I thought you meant them using implode in that example was the bug.

[bfarber]

You shouldn't use prepared statements to replace the IN value (you will be passing a string, so it will end up as "IN('1,2,3')" which isn't what you want). Anywhere we do it is likely a bug and can be reported.

[Midnight Modding]

8 hours ago, bfarber said:

You shouldn't use prepared statements to replace the IN value (you will be passing a string, so it will end up as "IN('1,2,3')" which isn't what you want). Anywhere we do it is likely a bug and can be reported.

Oh, because of it being a string, rather than IN(1,2,3) is what causes it to behave incorrectly? So that is why it was returning only one row, it was converting '1,2,3' to 1. right?

I found quite a few of them where it was using it in a prepared statement, but this is 4.2.9. And a lot of them were using a query for it, so I am not sure if maybe you had it set where it would work with a query in the IN clause for prepared statements.

It's really kind of nice using the IPS method, anyway. just passing an array and the rest done.

[Midnight Modding]

There's no issue with the following, is there?

$where[] = array( \IPS\Db::i()->in( 'test_one', $x ) . ' or ' . \IPS\Db::i()->in( 'test_two', $x ) );

If I do multiple elements of $where it would automatically use "and", so I assume this is pretty much the main way to use OR with 2 in() calls. (I am not quite to a point of being able to test it yet, but I think it would work fine).

What would have been cool is if the field name passed to in() could take an array, but I assume it would be very rare where someone would be checking multiple fields against the same array. ?

What I see a lot of in first party files is:

array( 'field IN(?)', \IPS\Db::i()->select( ... ) );

So is that a situation where it does work with prepared statements, or that shouldn't be done, either? (just being sure there's not something with select() that has it where it doesn't use a string, but I assume you're saying prepared statements would always be causing it to be a string).

I've done it several times and thought it was working, but maybe not.

I occasionally see IPS doing the following:

\IPS\Db::i()->in( 'some_id', iterator_to_array( \IPS\Db::i()->select( ... ) ) );

but not as often as the previous way.

[bfarber]

Our code to manage prepared statements handles \IPS\Db\Select instances special. The second format is just passing an array to the in() method. The first method uses a sub-select while the second method uses a traditional in() clause.

WHERE field IN(SELECT id FROM other_table)
//vs
WHERE field IN(1,2,3)

 

[Midnight Modding]

15 hours ago, bfarber said:

Our code to manage prepared statements handles \IPS\Db\Select instances special. The second format is just passing an array to the in() method. The first method uses a sub-select while the second method uses a traditional in() clause.

WHERE field IN(SELECT id FROM other_table)
//vs
WHERE field IN(1,2,3)

 

Is there much of any advantage in one way vs. the other? I guess I would think, theoretically, a subquery would be slightly better, but I haven't used any in the past. I had read that apparently sometimes a subquery isn't faster, but I can't see a reason it would actually be "slower", so the only thing I can think of for using the separate query is maybe if you needed to store it in a variable and use it in another way before even calling in()?

[bfarber]

Using a list of ids in an IN() clause is typically more performant than a subquery, unless the number of ids is extremely large (or can be extremely large) which can cause SQL errors (e.g. max packet size exceeded), in which cause using a subselect is the next best option.

[Midnight Modding]

17 hours ago, bfarber said:

Using a list of ids in an IN() clause is typically more performant than a subquery, unless the number of ids is extremely large (or can be extremely large) which can cause SQL errors (e.g. max packet size exceeded), in which cause using a subselect is the next best option.

Thanks for all of the help. I'm finally quite happy with my app and my overall comfort level with working with the 4.x framework. Most everything feels easy now. I can't think of anything I'm unsure on. I haven't technically done the upgrade routine yet, but I figure that should be pretty easy. ?