Hi All,

At the moment I've got the following problem and hope someone could give me a hint/pointer where to start on this.

I have the following tables: Employees Skills EmployeeSkill (table for the n:n relation)

I now want to select all employees with both Skill A (SkillId=12) & Skill B (SkillId = 17).

Now I tried this:

PredicateExpression skillFilter = new PredicateExpression();
skillFilter.AddWithAnd(EmployeeSkillFields.SkillId == 12);
skillFilter.AddWithAnd(EmployeeSkillFields.SkillId == 17);

RelationCollection joins = new RelationCollection();
joins.Add(EmployeeEntity.Relations.EmployeeSkillEntityUsingEmployeeId, JoinHint.Left);

SortExpression sort = new SortExpression((EmployeeFields.Surname | SortOperator.Ascending));
emps.GetMulti(skillFilter ,-1,sort,joins);
return emps;

This always return 0 (zero) results.

And when I have resolved this I have an even tricker one, on which someone might know a starting point:

I also have: Employee Language EmployeeLanguage (relation table for n:n + extra attributes)

Each EmployeeLanguageEntity has the following fields: LanguageId (FK) EmployeeId (FK) WritingLevel (byte) ReadingLevel (byte) SpeakingLevel (byte)

Now I have the same need to filter as on Skills but now I also need to assess the level is >= some variable, let's say 3 on a scale from 0-5. Again this most be possible for multiple languages at the same time like: Dutch reading >= 5 && English speaking >= 2

Any suggestions would be greatly appericiated.

Cheers, Gab

For your first question, see this recent thread:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8919

I think your second question is more complex, but the same general solution(s) discussed in the above thread should still apply.

HTH,

Phil

Also, if you want all values returned, please specify 0 instead of -1

Hi,

For you first question, the only odd thing i see is :

emps.GetMulti(skillFilter ,-1,sort,joins);

You may set 0 instead of -1...

For your second question, you will use 2 or more relations from Employee to EmployeeLanguage. For doing that, you must use aliases :

RelationCollection joins = new RelationCollection();
joins.Add(EmployeeEntity.Relations.EmployeeLanguageEntityUsingEmployeeId,"Dutch" JoinHint.Left);
joins.Add(EmployeeEntity.Relations.EmployeeLanguageEntityUsingEmployeeId,"English" JoinHint.Left);

and the same for predicates :

filter.AddWithAnd(New FieldCompareValue(EmployeeLanguageFields.LanguageId,ComparisonOperator.Equals , 3,"Dutch");
filter.AddWithAnd(New FieldCompareValue(EmployeeLanguageFields.ReadingLevel ,ComparisonOperator.GreaterEqual , 2,"Dutch");

FieldCompareValue(EmployeeLanguageFields.LanguageId,ComparisonOperator.Equals , 4,"English");
filter.AddWithAnd(New FieldCompareValue(EmployeeLanguageFields.ReadingLevel ,ComparisonOperator.GreaterEqual , 5,"English");

Cheers

Hi All,

Thanks for all the input.

I found the http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8919 thread to be most usefull. Also comments on aliases gave me a nice extra bit of knowledge.

I will use the FieldCompareSetPredicate solution. As I think this will do exactly what I need.

Thanks a lot for the help!