Hi,
My friend has a problem with forum registration (he waits for activation link since 12:00), so he asked me to paste his question.
My goal is to achieve query like this:
SELECT "content"."humans"."birth_date" AS "BirthDate", "content"."humans"."birth_place" AS "BirthPlace",
"content"."humans"."comments" AS "Comments", "content"."humans"."created_at" AS "CreatedAt", "content"."humans"."created_by" AS "CreatedBy",
"content"."humans"."death_date" AS "DeathDate", "content"."humans"."death_place" AS "DeathPlace", "content"."humans"."foreign_id" AS "ForeignId",
"content"."humans"."human_default_full_name" AS "HumanDefaultFullName", "content"."humans"."id" AS "Id", "content"."humans"."id_sex" AS "IdSex",
"content"."humans"."modified_at" AS "ModifiedAt", "content"."humans"."modified_by" AS "ModifiedBy" FROM "content"."humans"
WHERE ( ( "content"."humans"."id" IN
(
SELECT "LPA_h1"."id" AS "Id"
FROM (( "content"."humans" "LPA_h1" CROSS JOIN "content"."humans" "LPA_h2" )
INNER JOIN "content"."person_names" "LPA_p3" ON "LPA_h1"."id"="LPA_p3"."id_human")
WHERE ( "LPA_h1"."human_default_full_name" % "LPA_h2"."human_default_full_name" AND "LPA_p3"."id_workflow_state" = 'PNW_NEW')
LIMIT 10000
)
) )
Important thing in this query is the cross join and inner join in subquery, and also LIMIT clause at the end.
I've red some posts on this forum about including limit clause in non-efficient-join-subquery and avoiding DISTINCT and I've found that there's solution to do that by setting
DynamicQueryEngineBase.DistinctFilteringPreferenceDefault property to AlwaysClientSide.
using (var adapter = this.CreateAdapter())
{
DynamicQueryEngineBase.DistinctFilteringPreferenceDefault = DistinctFilteringPreferenceType.AlwaysClientSide;
this.SetSimilarity(adapter, similarityLevel);
adapter.FetchEntityCollection( ..., maxResultCount);
}
This usage causes that DISTINCT isn't included, but there's no LIMIT also:>
How to get LIMIT clauses and NO-DISTINCT and to work together???
Thx in advance.
