Filtering on a One-to-Many Field

6 posts, 0 answers
  1. Mark
    Mark avatar
    5 posts
    Member since:
    Apr 2012

    Posted 23 Sep 2013 Link to this post

    I have a field in the table that is 'Licensed In State'. A person can be licensed in more than one state. How in the RadFilter do I search for those people that hold licenses in both TX and AZ? Simply ANDing them won't work.
  2. Mark
    Mark avatar
    5 posts
    Member since:
    Apr 2012

    Posted 25 Sep 2013 Link to this post

    Is the reason there is no response because you can't filter on one-to-many fields from a db? All your example demos use one-to-one fields.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Angel Petrov
    Admin
    Angel Petrov avatar
    1006 posts

    Posted 26 Sep 2013 Link to this post

    Hello Mark,

    I am not quite sure that I fully understand the scenario. Could you please provide a more detail explanation of the exact requirement? Additionally if you could show us the table definitions and relations that would be great. Once we have a better understanding of the issue we should be able to provide a precise answer.

    Regards,
    Angel Petrov
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  5. Mark
    Mark avatar
    5 posts
    Member since:
    Apr 2012

    Posted 26 Sep 2013 Link to this post

    Using RadGrid / RadFilter or any other control....

    Id     Name     State
    1     John     CO
    1     John     AZ
    1     John     NY
    3     Sally     SD
    3     Sally     CO

    I need to find someone who is in both states CO and NY. That should return just "John".

    SQL would look like the below

    select Distinct
        Name
    from License
    where exists( select * from License as lic2 where lic2.State = 'CO' and lic2.Name = License.Name)
      and exists( select * from License as lic2 where lic2.State = 'NY' and lic2.Name = License.Name)
  6. Mark
    Mark avatar
    5 posts
    Member since:
    Apr 2012

    Posted 30 Sep 2013 Link to this post

    @Angel Petrov - Well?

    I'm guessing Telerik does not handle any one-to-many situations? Fine, just say so.
  7. Angel Petrov
    Admin
    Angel Petrov avatar
    1006 posts

    Posted 01 Oct 2013 Link to this post

    Hello Mark,

    Indeed such a functionality is not available out of the box. However such behavior can be implemented with the help of a RadFilter and RadGrid. The idea here is to group the items by Name and use a RadFilter with an OR RadFilterGroupOperation to filter the grid. Once the data is filtered we can check whether the number of items in the group is equal to the filter expressions count if that is the case this means that the name is the one we are looking for. Following this approach I have assembled a sample website which can be found in attachments.

    Regards,
    Angel Petrov
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017