On Mar 4, 11:15 am, kdf <[email protected]> wrote:
> Thanks Jeremy, that works.
>
> I had found a similar example but I was hoping for a simpler
> approach. The majority of my legacy tables have composite keys and
> effective dating so this adds a lot of potential for error in my code.
>
> How hard would it be to write a plugin to generate this code
> automatically ?
Depends how similar the different cases are. If the columns have the
same name in all tables, it shouldn't be difficult:
class Sequel::Dataset
# With fieldname and fieldvalue
def wfnfv(n, v)
s = first_source_alias
filter(:fieldname.qualify(s)=>n,
:fieldvalue.qualify(s)=>v)
end
# Latest by effective date
def lbed
s = first_source
a = unused_table_alias(s)
filter(:effdt.qualify(s)=>DB[s.as(a)].
select{max(:effdt.qualify(a))}.
filter(:fieldname.qualify(s)=>:fieldname.qualify(a),
:fieldvalue.qualify(s)=>:fieldvalue.qualify(a)))
end
end
DB[:mytable].wfnfv('X', 'Y').lbed
SELECT *
FROM mytable
WHERE (((mytable.fieldname = 'X')
AND (mytable.fieldvalue = 'Y'))
AND (mytable.effdt IN (
SELECT max(mytable_0.effdt)
FROM mytable AS mytable_0
WHERE ((mytable.fieldvalue = mytable_0.fieldvalue)
AND (mytable.fieldname = mytable_0.fieldname)))))
Obviously if the columns have different names in all the tables,
you'll have more method arguments. Note that the code above requires
3.9.0, which will be released today.
Jeremy
--
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.
