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

[geffchang]

It seems MediaContainer.medias is unsearchable. So, you can't do FlexSearch on it.

An alternative would be to export it instead. This might help:

INSERT_UPDATE MediaContainer;catalogVersion(catalog(id),version)[unique=true];qualifier[unique=true];medias(code)
"#% impex.exportItemsFlexibleSearch(""SELECT {pk} FROM {MediaContainer AS mc} WHERE {mc.qualifier}='QUALIFIER'"");"

Let me know if the answer helped you. If it does, don't forget to upvote and accept the answer.

[former_member1317504]

Hi Chang,

Thanks for the answer. I want to export Media and its attributes by fetching all Medias present in a MediaContainer.

There is a one to many relation existing in MediaContainer2MediaRel where source is 'MediaContainer' and target is 'Media'. so i was using this relation.