Showing results for 
Search instead for 
Did you mean: 

Flexisearch: how to do join on column which is not defined in type definition but is there is database?

Former Member
0 Kudos

I need to do join on database column which is not there in type defination. for example to get address that belongs to user the native sql would be

  SELECT u.*

FROM Users as u INNER JOIN addresses as address on address.ownerPkString = WHERE = '8796158656516' ;

I need to do the same in flexi search so I have syntax like

SELECT {u:uid} FROM { User as u JOIN Address as address ON {address.ownerPkString} = {} } WHERE {} = '8796158656516'

But it is giving error
Unknown column 'address.ownerPkString' in 'on clause

Which I can apprehend as ownerPkString is not defined in Address type definition.

But what is the way of doing the same in flexi search?

Any input would be appreciated.

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi vikram,

Please try following things

If you need user based on his one of the address pk, then use this query.

select {pk} from {user as u JOIN Address as addr on {u:pk}={addr:owner}} where {addr:pk}='8796158656516'

If you need address list based on user pk, then try this query.

select {pk} from {Address as addr JOIN User as u on {addr:owner}={u:pk}} where {u:pk}='8796158656516'



Former Member
0 Kudos

Thanks a lot it works perfectly 🙂

I was missing that I need to user item attribute "owner" rather than direct db columnName "ownerPkString".

Answers (0)