I finally got some Sequel code working to replace a honkin' big query
in an old MSSQL stored proc. I would appreciate any advice someone may
offer on a better way to achieve the same goal.
Following the Sequel code is the original SQL from the MSSQL stored
proc for reference.
# Working query (using grep for postgresql case-insensitive matches
with future support for case-sensitive)
def search
s = /#{request['q']}/i
lang_id = request['langID']
lang_id = nil if lang_id && lang_id.empty?
@lang = OJLanguage[lang_id] if lang_id
@title = "Search of ObjJob for '#{request['q']}' #{" in #
{[email protected]_name}" if @lang}"
valid_o = @lang ? @lang.objects_dataset : OJObject.dataset
valid_m = OJMethod.filter( :object_id=>valid_o.select(:id) )
valid_p = OJProperty.filter( :object_id=>valid_o.select(:id) )
valid_c = OJConstant.filter( :object_id=>valid_o.select(:id) )
valid_a = OJArgument.filter( :method_id=>valid_m.select(:id) )
nulli = "NULL::integer"
no_o = "#{nulli} AS o_id"
no_p = "#{nulli} AS p_id"
no_m = "#{nulli} AS m_id"
no_c = "#{nulli} AS c_id"
o_name = valid_o.grep( :name, s ).select( "60 AS weight, id
AS o_id, #{no_m}, #{no_p}, #{no_c}".lit )
o_view = valid_o.grep( :overview, s ).select( "30 AS weight, id
AS o_id, #{no_m}, #{no_p}, #{no_c}".lit )
o_desc = valid_o.grep( :description, s ).select( "10 AS weight, id
AS o_id, #{no_m}, #{no_p}, #{no_c}".lit )
m_name = valid_m.grep( :name, s ).select( "60 AS weight, #
{no_o}, id AS m_id, #{no_p}, #{no_c}".lit )
m_view = valid_m.grep( :overview, s ).select( "20 AS weight, #
{no_o}, id AS m_id, #{no_p}, #{no_c}".lit )
m_desc = valid_m.grep( :description, s ).select( " 5 AS weight, #
{no_o}, id AS m_id, #{no_p}, #{no_c}".lit )
a_name = valid_a.grep( :name, s ).select( "10 AS weight, #
{no_o}, method_id AS m_id, #{no_p}, #{no_c}".lit )
a_view = valid_a.grep( :overview, s ).select( " 5 AS weight, #
{no_o}, method_id AS m_id, #{no_p}, #{no_c}".lit )
p_name = valid_p.grep( :name, s ).select( "60 AS weight, #
{no_o}, #{no_m}, id AS property_id, #{no_c}".lit )
p_view = valid_p.grep( :overview, s ).select( "30 AS weight, #
{no_o}, #{no_m}, id AS property_id, #{no_c}".lit )
p_desc = valid_p.grep( :description, s ).select( "10 AS weight, #
{no_o}, #{no_m}, id AS property_id, #{no_c}".lit )
c_name = valid_c.grep( :name, s ).select( "70 AS weight, #
{no_o}, #{no_m}, #{no_p}, id AS constant_id".lit )
c_view = valid_c.grep( :overview, s ).select( "30 AS weight, #
{no_o}, #{no_m}, #{no_p}, id AS constant_id".lit )
weights_on_ids = [ o_name, o_view, o_desc,
m_name, m_view, m_desc, a_name, a_view,
p_name, p_view, p_desc,
c_name, c_view ].inject{ |all,ds| all.union
(ds) }.as(:weights_on_ids)
weighted_ids = DB.dataset.from( weights_on_ids )
.select( "SUM(weight) AS
weight".lit, :o_id, :m_id, :p_id, :c_id )
.group_by( :o_id, :m_id, :p_id, :c_id )
.order( :weight.desc ).as( :weighted_ids )
@results = DB.dataset.from( weighted_ids )
.left_outer_join
( :objects, :id=>:weighted_ids__o_id )
.left_outer_join
( :methods, :id=>:weighted_ids__m_id )
.left_outer_join
( :properties, :id=>:weighted_ids__p_id )
.left_outer_join
( :constants, :id=>:weighted_ids__c_id )
.select(
:weight,
:o_id, :objects__name___o_name,
:objects__overview___o_overview,
:m_id, :methods__name___m_name,
:methods__overview___m_overview,
:p_id, :properties__name___p_name,
:properties__overview___p_overview,
:c_id, :constants__name___c_name,
:constants__overview___c_overview
)
.all
end
# Original MSSQL stored proc:
IF @langID IS NULL
SELECT foo.weight,
foo.objID,o.objName,
foo.methID,m.methodName,
foo.propID,p.propName,
foo.constID,c.constName,
foo.langID,t_Languages.langName
FROM (
SELECT SUM(weight) AS weight,langID,objID,methID,propID,constID
FROM (
SELECT langID,objID,NULL AS methID,NULL AS propID,NULL AS
constID, 60 AS weight FROM t_Objects o WHERE objName LIKE
@searchString
UNION
SELECT langID,objID,NULL,NULL,NULL,30 AS weight FROM t_Objects o
WHERE overview LIKE @searchString
UNION
SELECT langID,objID,NULL,NULL,NULL,10 AS weight FROM t_Objects o
WHERE description LIKE @searchString
UNION
SELECT langID,NULL,id,NULL,NULL,60 AS weight FROM t_Methods m
INNER JOIN t_Objects o ON m.objID=o.objID WHERE methodName
LIKE @searchString
UNION
SELECT langID,NULL,id,NULL,NULL,20 AS weight FROM t_Methods m
INNER JOIN t_Objects o ON m.objID=o.objID WHERE m.overview
LIKE @searchString
UNION
SELECT langID,NULL,id,NULL,NULL,5 AS weight FROM t_Methods m
INNER JOIN t_Objects o ON m.objID=o.objID WHERE m.description
LIKE @searchString
UNION
SELECT langID,NULL,methodID,NULL,NULL,10 AS weight FROM
t_MethodArguments a
INNER JOIN t_Methods m ON a.methodID=m.id
INNER JOIN t_Objects o ON m.objID=o.objID WHERE argName LIKE
@searchString
UNION
SELECT langID,NULL,methodID,NULL,NULL,5 AS weight FROM
t_MethodArguments a
INNER JOIN t_Methods m ON a.methodID=m.id
INNER JOIN t_Objects o ON m.objID=o.objID WHERE a.overview
LIKE @searchString
UNION
SELECT langID,NULL,NULL,id,NULL,60 AS weight FROM t_Properties p
INNER JOIN t_Objects o ON p.objID=o.objID WHERE propName LIKE
@searchString
UNION
SELECT langID,NULL,NULL,id,NULL,30 AS weight FROM t_Properties p
INNER JOIN t_Objects o ON p.objID=o.objID WHERE p.overview
LIKE @searchString
UNION
SELECT langID,NULL,NULL,id,NULL,10 AS weight FROM t_Properties
p
INNER JOIN t_Objects o ON p.objID=o.objID WHERE p.description
LIKE @searchString
UNION
SELECT langID,NULL,NULL,NULL,id,70 AS weight FROM t_Constants c
INNER JOIN t_Objects o ON c.objID=o.objID WHERE c.constName
LIKE @searchString
UNION
SELECT langID,NULL,NULL,NULL,id,30 AS weight FROM t_Constants c
INNER JOIN t_Objects o ON c.objID=o.objID WHERE c.overview
LIKE @searchString
) every
GROUP BY langID,objid,methid,propID,constID
) foo
INNER JOIN t_Languages ON foo.langID=t_Languages.langID
LEFT OUTER JOIN t_Objects o ON foo.objID=o.objID
LEFT OUTER JOIN t_Methods m ON foo.methID=m.id
LEFT OUTER JOIN t_Properties p ON foo.propID=p.id
LEFT OUTER JOIN t_Constants c ON foo.constID=c.id
ORDER BY weight DESC,langName,objName,methodName,propName,constName
ELSE
SELECT foo.weight,
foo.objID,o.objName,
foo.methID,m.methodName,
foo.propID,p.propName,
foo.constID,c.constName
FROM (
SELECT SUM(weight) AS weight,objID,methID,propID,constID FROM (
SELECT objID,NULL AS methID,NULL AS propID,NULL AS constID,60 AS
weight FROM t_Objects o WHERE lang...@langid AND objName LIKE
@searchString
UNION
SELECT objID,NULL,NULL,NULL,30 AS weight FROM t_Objects o WHERE
lang...@langid AND overview LIKE @searchString
UNION
SELECT objID,NULL,NULL,NULL,10 AS weight FROM t_Objects o WHERE
lang...@langid AND description LIKE @searchString
UNION
SELECT NULL,id,NULL,NULL,60 AS weight FROM t_Methods m
INNER JOIN t_Objects o ON m.objID=o.objID WHERE
o.lang...@langid AND methodName LIKE @searchString
UNION
SELECT NULL,id,NULL,NULL,20 AS weight FROM t_Methods m
INNER JOIN t_Objects o ON m.objID=o.objID WHERE
o.lang...@langid AND m.overview LIKE @searchString
UNION
SELECT NULL,id,NULL,NULL,5 AS weight FROM t_Methods m
INNER JOIN t_Objects o ON m.objID=o.objID WHERE
o.lang...@langid AND m.description LIKE @searchString
UNION
SELECT NULL,methodID,NULL,NULL,10 AS weight FROM
t_MethodArguments a
INNER JOIN t_Methods m ON a.methodID=m.id
INNER JOIN t_Objects o ON m.objID=o.objID WHERE
o.lang...@langid AND argName LIKE @searchString
UNION
SELECT NULL,methodID,NULL,NULL,5 AS weight FROM
t_MethodArguments a
INNER JOIN t_Methods m ON a.methodID=m.id
INNER JOIN t_Objects o ON m.objID=o.objID WHERE
o.lang...@langid AND a.overview LIKE @searchString
UNION
SELECT NULL,NULL,id,NULL,60 AS weight FROM t_Properties p
INNER JOIN t_Objects o ON p.objID=o.objID WHERE
o.lang...@langid AND propName LIKE @searchString
UNION
SELECT NULL,NULL,id,NULL,30 AS weight FROM t_Properties p
INNER JOIN t_Objects o ON p.objID=o.objID WHERE
o.lang...@langid AND p.overview LIKE @searchString
UNION
SELECT NULL,NULL,id,NULL,10 AS weight FROM t_Properties p
INNER JOIN t_Objects o ON p.objID=o.objID WHERE
o.lang...@langid AND p.description LIKE @searchString
UNION
SELECT NULL,NULL,NULL,c.id,70 AS weight FROM t_Constants c
INNER JOIN t_Objects o ON c.objID=o.objID WHERE
o.lang...@langid AND c.constName LIKE @searchString
UNION
SELECT NULL,NULL,NULL,c.id,30 AS weight FROM t_Constants c
INNER JOIN t_Objects o ON c.objID=o.objID WHERE
o.lang...@langid AND c.overview LIKE @searchString
) every
GROUP BY objid,methid,propID,constID
) foo
LEFT OUTER JOIN t_Objects o ON foo.objID=o.objID
LEFT OUTER JOIN t_Methods m ON foo.methID=m.id
LEFT OUTER JOIN t_Properties p ON foo.propID=p.id
LEFT OUTER JOIN t_Constants c ON foo.constID=c.id
ORDER BY weight DESC,objName,methodName,propName,constName
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
