FIND_IN_SET

panzram · September 2018

Hi,

I have two simple tables:

TYPES
id,type
1,'one'
2,'two'
3,'three'

USERS
id,name,exclude_types
1,'n1','2,3'
2,'n2',''
3,'n3',''

I have an sql that looks like this:

SELECT t.id, t.type FROM types t, users u WHERE u.id=1 and FIND_IN_SET(t.id, u.exclude_types) = 0

How can I achieve this serverside?

Editor::inst( $db, 'types', 'id')
->fields(
Field::inst( 'id' ),
Field::inst( 'type' )
)
->process( $_POST )
->json();

I've tried to add a where clause with a function and a subset:

->where( function ( $q ) {
$q->where( 'id', '(SELECT exclude_types FROM users WHERE id='.$user_id, 'FIND_IN_SET', false );
} )

It is not working.

Any help is appreciated!

allan · September 2018

It looks like that would give an SQL syntax error. There is a missing closing parenthesis:

->where( function ( $q ) {
  $q->where( 'id', '(SELECT exclude_types FROM users WHERE id='.$user_id.')', 'FIND_IN_SET', false );
} )

I'm not certain that will be the fix though - your original query doesn't use a sub-select.

It might be that you would be best to create a VIEW in this case and read from that.

Allan

Replies