Query multiple ContentTypes and filter on each of the different ContentTypes.

Topics: General
Sep 13, 2013 at 8:34 PM
Hi,

I have 3 Content Types: MyType1, MyType2, MyType3 and I want to query(via the ContentManager.Query method) them into a single list.

I want this list to be filterable on each of the different ContentParts on the ContentTypes.
var query = _cm.Query(new string[] { "MyType1", "MyType2", "MyType3"})
  .Where<MyPart1Record>(
    x => ( x.MY_BOOLEAN_FIELD == (search.MySuppliedBooleanValue == "Y") ) &&
         (
           string.IsNullOrEmpty(search.Expression) ||
           (
             x.MY_SEARCHABLE_FIELD1.Contains(search.Expression) ||
             x.MY_SEARCHABLE_FIELD2.Contains(search.Expression)
           )
         )
  )
  .Where<MyPart2Record>(
    x => ( x.MY_BOOLEAN_FIELD == (search.MySuppliedBooleanValue == "Y") ) &&
         (
           string.IsNullOrEmpty(search.Expression) ||
           (
             x.MY_SEARCHABLE_FIELD1.Contains(search.Expression) ||
             x.MY_SEARCHABLE_FIELD2.Contains(search.Expression)
           )
         )
  )
  .Where<MyPart3Record>(
    x => ( x.MY_BOOLEAN_FIELD == (search.MySuppliedBooleanValue == "Y") ) &&
         (
           string.IsNullOrEmpty(search.Expression) ||
           (
             x.MY_SEARCHABLE_FIELD1.Contains(search.Expression) ||
             x.MY_SEARCHABLE_FIELD2.Contains(search.Expression)
           )
         )
  );
So, each of the ContentTypes have several ContentParts attached to them. In this example the ContentType MyType1 has the MyPart1 with the corresponding MyPart1Record, ContentType MyType2 has the MyPart2 with the corresponding MyPart2Record, etc.

The query above results in a SQL query which doesn't work because of 2 things:

1) INNER JOINS are used to join the ContentTypes data
2) AND's are used in the where clauses

Generated pseudo query:
SELECT
FROM
  orchard_framework_contentitemversionrecord
  inner join orchard_framework_contentitemrecord
  inner join mypart1record
  inner join mypart2record
  inner join mypart3record
  inner join orchard_framework_contenttyperecord
where
  orchard_framework_contenttyperecord.name in ( 'MyType1', 'MyType2', MyType3' ) AND
  expressions for MyPart1Record AND
  expressions for MyPart2Record AND
  expressions for MyPart3Record AND
  orchard_framework_contentitemversionrecord.published = 1
The following, pseudo, query would give the wanted results:
SELECT
FROM
  orchard_framework_contentitemversionrecord
  inner join orchard_framework_contentitemrecord
  left outer join mypart1record
  left outer join mypart2record
  left outer join mypart3record
  inner join orchard_framework_contenttyperecord
where
  orchard_framework_contenttyperecord.name in ( 'MyType1', 'MyType2', MyType3' ) AND
  (
    ( expressions for MyPart1Record ) OR
    ( expressions for MyPart2Record ) OR
    ( expressions for MyPart3Record ) 
  ) AND
  orchard_framework_contentitemversionrecord.published = 1
So the way the .Where Lambda's are working now won't give me any results because the different ContentTypes don't have ALL the different Parts on them, which is expected I suppose.

So how would I accomplish this usecase: Query multiple ContentTypes to a single list and be able to filter on each of the different ContentTypes.
Sep 13, 2013 at 8:53 PM
I guess you can do three queries and collect the result in a IContent collection.