What could be better than googling for help and finding your own notes?
I'm adding another scenario that's just shown itself as a related problem
with an empty *scopes* in apply_scopes in Rails 4.0.x:
I have a User model that *has_many* Filings.
In an index method, I had hoped to do this:
scopes = {
search: [params[:search], :description],
order_by: parse_sort_param(:date_submitted, :description)
}
@filings = current_user.filings.apply_scopes(scopes)
but, this doesn't work when *scopes *is empty: The query simply returns
*all* the filings, not just those specific to a user.
I guess, as in my original posting below, this is effectively calling a .
*where* clause before calling the .*apply_scopes*, and that DOESN'T WORK.
*apply_scopes
must be called before a .where clause. *
Instead, I have to do this:
@filings = Filing.apply_scopes(scopes).where( user_id: current_user.id )
Not quite as nice, but it works correctly when *scopes* is empty.
Tim
On Tuesday, April 21, 2015 at 9:46:59 PM UTC-4, Tim Griffin wrote:
>
> This is not a problem looking for a solution, but just an observation that
> might save someone else some head-scratching.
>
> This issue has been reported as Issue 152
> <https://github.com/Hobo/hobo/issues/152>.
>
> *It seems that it is very important to call .apply_scopes BEFORE calling a
> .where clause, especially if the scope is effectively empty. *
>
> Calling .*apply_scopes* with an empty scope AFTER a .*where* clause
> causes the .where clause to be ignored!
>
> An example:
>
> In an #index function, I watch for search and sorting scopes:
>
> def index
>
> scopes = {
>
> :search => [params[:search], :user_name, :search_text],
> :order_by => parse_sort_param(:id, :date_searched, :user_name,
> :matches, :search_text)
> }
> @searches = Search
> .where('date_searched >= ? AND date_searched <= ?',
> params[:start_date], params[:end_date] )
> .apply_scopes(scopes).order('date_searched DESC')
> .paginate(:per_page => 200, :page => params[:page])
>
>
> I've found, though, that if my request does *not* include a search and an
> order_by parameter, the call to *apply_scopes *causes the .where clause
> to be ignored!
>
> For example, this is the request to filter by date:
>
> GET "/admin/searches?start_date=04%2F01%2F2015&end_date=04%2F30%2F2015"
>
> And this is the SQL (for SQL Server) that's generated:
>
> [INFO ] Parameters: {"start_date"=>"04/01/2015",
> "end_date"=>"04/30/2015"}
> [DEBUG] Search Load (98.4ms) EXEC sp_executesql N'SELECT TOP (200)
> [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY date_searched DESC) AS
> [__rn], [searches].* FROM [searches] ) AS [__rnt] WHERE [__rnt].[__rn] >
> (0) ORDER BY [__rnt].[__rn] ASC'
>
> How bizarre!
>
> But, if it *does* include a search parameter to be used by apply_scopes:
>
> [INFO ] GET
> "/admin/searches?start_date=04%2F01%2F2015&end_date=04%2F30%2F2015&
> search=Tim"
>
> I get this SQL:
>
> [DEBUG] Search Load (8.8ms) EXEC sp_executesql N'SELECT TOP (200)
> [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY date_searched DESC) AS
> [__rn], [searches].* FROM [searches] WHERE (date_searched >=
> N''04/01/2015'' AND date_searched <= N''04/30/2015'') AND
> (((searches.user_name LIKE N''%Tim%'') OR (searches.search_text LIKE
> N''%Tim%''))) ) AS [__rnt] WHERE [__rnt].[__rn] > (0) ORDER BY
> [__rnt].[__rn] ASC'
>
>
> SOLUTION:
>
> If I *reverse* the order of the .where and the .apply_scopes, it works as
> expected, which is completely unintuitive!
>
> @searches = Search
> .apply_scopes(scopes)
> .order('date_searched DESC')
> .where('date_searched >= ? AND date_searched <= ?',
> params[:start_date], params[:end_date] )
>
> [DEBUG] Search Load (6.5ms) EXEC sp_executesql N'SELECT TOP (200)
> [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY date_searched DESC) AS
> [__rn], [searches].* FROM [searches] WHERE (date_searched >=
> N''04/01/2015'' AND date_searched <= N''04/30/2015'') ) AS [__rnt] WHERE
> [__rnt].[__rn] > (0) ORDER BY [__rnt].[__rn] ASC'
>
> In thinking about crafting this query, it made more sense to me to reduce
> the overall set first (with the .where clause), and then apply any search
> (or sorting) within that.
>
> I had no idea that the order of .where and .apply_scopes was so important!
>
>
> Tim
>
>
--
You received this message because you are subscribed to the Google Groups "Hobo
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to hobousers+unsubscr...@googlegroups.com.
To post to this group, send email to hobousers@googlegroups.com.
Visit this group at http://groups.google.com/group/hobousers.
For more options, visit https://groups.google.com/d/optout.
