Creating a Generative Search with Elixir

Question

I'm stuck using Elixir and I currently have a really messy way of searching through a database that i'd like to improve. The documentation provides insight on how to do a basic generative search but I need to step through many different classes and i'd prefer to use Elixir rather than scanning through the list myself.

Here's an example:

Class Student:
hobby = Field(String)
additional_info = OneToOne('AdditionalInformation', inverse='student')
user_profile = OneToOne('UserProfile', inverse='student')

Class AdditionalInformation:
 state = Field(String)
 city = Field(String)
 student = OneToOne('Student', inverse='additional_info')

Class UserProfile:
 username = Field(String)
 date_signed_up = Field(DateTime)
 student = OneToOne('Student', inverse = 'user_profile')

In this example, i'd like to find all students that:

1. Signed up after 2008
2. Are from California
3. Have "video games" as their hobby

I'm thinking there should be a way for me to go:

result = UserProfile.query.filter_by(date_signed_up>2008)
result.query.filter_by(UserProfile.student.hobby='blabla')
result.query....

Currently i'm putting them into a list and looking for a set.

Answer 1

I haven't used Elixir, but I have used SQLAlchemy. I don't think you can do what you want given that current setup. As far as I know, there is no way to filter by relationships directly.

It's unclear whether you're creating new tables or dealing with existing ones, so I'm just going to throw some info at you and hope some of it is helpful.

You can join tables together in SQLAlchemy (assuming there's a foreign key called student_id on UserProfile). This would give you all students who signed up since 2008.

result = Student.query.join(UserProfile).filter(Student.id==UserProfile.student_id).filter(UserProfile.date_signed_up>2008).all()

You can chain .filter() together like I did above, or you can pass multiple args to them. I find this especially useful for dealing with unknown numbers of filters, like you'd get from a search form.

conditions = [UserProfile.date_signed_up>2008] 

if something_is_true:
  conditions.append(UserProfile.username=="foo")

result = Student.query.join(UserProfile).filter(Student.id==UserProfile.student_id).filter(and_(*conditions)).all()

There's also more complex stuff you can do with hybrid properties, but that doesn't seem appropriate here.