Indexing boolean in relation model

[Daniel Gottschalck]

Hey

In the website, there is "projects", and "projects" can have "courses".

In my project index file, how would I filter on "courses" which "is_visible" true?

Im confused on whether to use "has" or "indexes" for a relation model boolean value.

 

ThinkingSphinx::Index.define 'refinery/wayfinders/project', :with => :active_record do
indexes :name, sortable: true
indexes :city
indexes :description
indexes courses.name, as: :course_name

indexes courses.is_visible, as: :is_visible <------------------------ This one?

set_property :group_concat_max_len => 8192
has zip, external_id

 

has courses.is_visible, as: :is_visible, type: :boolean <------------------- Or this one?
end

And how would I then filter it in my search_controller? So I only get courses which are visible?

@project_search = Refinery::Wayfinders::Project.search(params[:search], :with => { :is_visible => true }) 

OR

@project_search = Refinery::Wayfinders::Project.search(params[:search], :include => :is_visible, :conditions => { :is_visible => true })

Or a third solution?

Thanks in advance

Regards 

 

[Pat Allan]

Hi Daniel

I’m not quite sure what results you’re expecting… projects with *only* visible courses? projects with at least one visible course? or something else?

But also, on a general level - boolean values like this definitely should be attributes - so, the `has` method, and are filtered using the `with` option.

Cheers

— 

Pat

--
You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
To unsubscribe from this group and stop receiving emails from it, send an email to thinking-sphi...@googlegroups.com.
To post to this group, send email to thinkin...@googlegroups.com.
Visit this group at http://groups.google.com/group/thinking-sphinx.
For more options, visit https://groups.google.com/d/optout.

[Daniel Gottschalck]

Hi Pat

Projects with only visible courses is what I need.

If I do:

has courses.is_visible, as: :is_visible

Then I'm getting an error about it expects a uint,  bigint or something else.

Regards

You received this message because you are subscribed to a topic in the Google Groups "Thinking Sphinx" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/thinking-sphinx/IoYl27QsSVI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to thinking-sphi...@googlegroups.com.

[Pat Allan]

So I think what you want is the following:

  # for PostgreSQL:

  has “bool_and(courses.is_visible)”, as: :is_visible, type: :boolean

  # for MySQL:

  has “IF(SUM(courses.is_visible) = COUNT(courses.is_visible), 1, 0)”, as: :is_visible, type: :boolean

Not entirely sure about the MySQL version, given Rails uses small integers instead of a boolean type… worth double-checking… but essentially we’re just falling back to SQL here, which is why we need to explicitly supply a type (using database columns normally, TS can figure it out).

Does that help?

— 

Pat

[Daniel Gottschalck]

Hey Pat

Thanks.

It looks like a rather difficult task to filter on a boolean in a relational model?

I will try it out when I get home and give a response, thanks.

Regards

[Pat Allan]

Sphinx has no concept of relationships between models/indices. So, with the attribute you’d had defined earlier, each project in Sphinx would have an array of integers (1 representing trues, 0 representing falses). There’s no connection between each of those values and the courses they originally came from.

With that standard attribute (no SQL snippet), it should still work, but the filter is saying “give me all projects where there is the value 1 (true) in the is_visible attribute.” This will return projects which have at least one visible course, but they may have invisible courses as well.

Although, now that I’m thinking about it, this should work:

has courses.is_visible, as: :is_visible

And then searching, use without instead of with:

Project.search ‘foo’, without: {is_visible: false}

So we’ve switched it to say “give me all projects where there is no value 0 (false) in the is_visible attribute.”

Hope this helps!

—
Pat

[Daniel Gottschalck]

Hi Pat

Appended your last solution:

ThinkingSphinx::Index.define 'refinery/wayfinders/project', :with => :active_record do
indexes :name, sortable: true
indexes :city
indexes :description
indexes courses.name, as: :course_name

set_property :group_concat_max_len => 8192
has zip, external_id

has courses.is_visible, as: :is_visible <---------------------
end

@project_search = Refinery::Wayfinders::Project.search(params[:search], without: { is_visible: false } )

But getting this error when I do "rake ts:rebuild":

ERROR: source 'refinery_wayfinders_project_core_0': expected attr type ('uint' or 'timestamp' or 'bigint') in sql_attr_multi, got 'bool is_visible from field'.
ERROR: index 'refinery_wayfinders_project_core': failed to configure some of the sources, will not index.

Regards

Den tirsdag den 17. juni 2014 20.56.23 UTC+2 skrev Daniel Gottschalck:

[Daniel Gottschalck]

Hi again

Tried with the solution you mentioned before aswell, with the IF = SUM etc,

same stuff, nothing happens, and the expected result is not there.

The SQL query in the log looks as follow:

Sphinx Query (0.8ms)  SELECT * FROM `refinery_wayfinders_project_core` WHERE MATCH('felttest') AND `is_visible` = 1 AND `sphinx_deleted` = 0 LIMIT 0, 20

"AND `is_visible`" is incorrect? It doesn't look at "refinery_wayfinders_courses.is_visible", but sees it as an attribute of refinery_wayfinders_project........

has "IF(SUM(refinery_wayfinders_courses.is_visible) = COUNT(refinery_wayfinders_courses.is_visible), 1, 0)", as: :is_visible, type: :boolean

Regards 

Den tirsdag den 17. juni 2014 20.56.23 UTC+2 skrev Daniel Gottschalck:

[Roger Kind Kristiansen]

I'm no expert and I'm not sure what is the problem with your current setup, just pitching in here since I've done something similar. Translating the solution I have to your setup, the way I've done it would look something like this:

has "COUNT(CASE courses.is_visible WHEN FALSE THEN 1 ELSE NULL END) > 0", :as => :is_visible, :type => :boolean

This attribute would be true only when all courses has is_visible set to true.

[Daniel Gottschalck]

Hey Roger

Thanks for your tip, ill try that out when I get home and give an update if it works or not :-)

Regards

[Daniel Gottschalck]

Hey again

Same stuff, the results isn't filtered...

This is still an issue, @Pat - you have any idea what I could try out?

Regards

[Roger Kind Kristiansen]

I noticed Pat is out travelling, so I thought I could mention another strategy I've had some luck with: Checking what is actually being indexed by extracting the query for the index from development.sphinx.conf and running it directly. Sometimes I've noticed that what is being indexed is not what I thought it was.

The index in your case is probably named  project_core_0 (?). You will have to remove the following part from the query for it to work: "BETWEEN $start AND $end"

[Daniel Gottschalck]

Hey

Thanks for answering.

I figured it out in another way.

Instead of searching for Courses through Projects, I did it the other way around, and searched through courses and grouping by projects.

Courses is more important. and if no course found, the project wont be shown either.

Regards

Den tirsdag den 17. juni 2014 20.56.23 UTC+2 skrev Daniel Gottschalck:

[Pat Allan]

Hi all

Roger, thanks for your suggestion, I’ve not had much time to deal with email over the last fortnight. That will slowly improve over the next week or two.

Daniel: Searching on courses certainly sounds like the better approach in the long run, so good to know you’ve got it working now :)

— 

Pat