Flexible search query to fetch all existing Medias from a Media Container

[former_member1317504]

I want to fetch all media present in media container using a flexible search query. I tried with this query

select {M.pk} from {Media AS M JOIN MediaContainer2MediaRel AS REL ON {REL.target}={M.PK} JOIN MediaContainer as MC ON {REL.source}={MC.PK}} WHERE {MC.qualifier} like '%OHTHH1_plp_container%'

I ended up with this error

Exception message : cannot search unknown field 'TableField(name='target',langPK='null',type=MediaContainer2MediaRel)' within type MediaContainer2MediaRel unless you disable checking , infoMap=TypeInfoMap for type = 8796093448274 code = MediaContainer2MediaRel superType = 8796093055058 itemTable = null ....

Please provide a solution for how to query to fetch all medias present in a media container.

[former_member685291 · Accepted solution]

Hi,

You don't need to include the relation itself in the JOIN. You may try the other way round:

SELECT {m:pk} 
FROM {Media AS m JOIN MediaContainer AS mc ON {m:mediaContainer} = {mc:pk}} 
WHERE {mc:qualifier} LIKE '%OHTHH1_plp_container%'

If you have Media sub-types which are in separate tables, you may need to create a query per each sub-type and then make a union of the results.

Best regards,

Kremena

[former_member1317504 · Accepted solution]

Thanks for the solution Kremena