On Thursday, August 14, 2014 1:27:00 PM UTC-7, Patrick Kuykendall wrote:
>
> Hello!
>
> I'm running into a little issue with update statements on models that I've
> aliased. Maybe I'm aliasing them the wrong way, I'm not sure. Would you
> mind taking a look? I'd love a little input. Thanks!
>
> In an earlier post (
> https://groups.google.com/forum/#!topic/sequel-talk/HB-rONDy_eg) I asked
> about using fully qualified names for joining models across databases, and
> this solution worked wonderfully. (Thanks again!)
>
> I've used that method on all of my models, but decided I'd like to alias
> the models as well to help clean up some of the join conditions and such.
> So, I've defined the models as such:
>
> class Table < Sequel::Model(DB.from(Sequel.qualify(:database,
> Sequel.qualify(Sequel.lit('[DBO]'), :table)).as(:tab)))
>
>
> When I call an for an update to a Table object, it results in SQL
> similar to this:
>
> UPDATE TOP (1) [DATABASE].[DBO].[TABLE] AS [TAB] SET [COLUMN] = 'value'
> WHERE [TABLEID] = 1
>
>
> Unfortunately, this syntax isn't acceptable for MSSQL, and I receive a
> TinyTDS error.
>
> Is there anyway to disable the alias for update statements, or should I be
> aliasing them in a different manner?
>
Quite a few databases support aliased tables in update statements, and
automatically unaliasing them is a bad idea, as the filter could be using
the alias.
Here's a hack that may work which will automatically unalias the table by
changing the update SQL produced by a dataset with an aliased table:
DB.extend_datasets do
private
def update_table_sql(sql)
sql << ' '
f = @opts[:from]
identifier_append(sql, unaliased_identifier(f.first))
end
end
but a better solution is just to override _update_dataset in the model:
class Table
private
def _update_dataset
ds = super
ds.from(Sequel.qualify(:database, Sequel.qualify(Sequel.lit('[DBO]'),
:table)))
end
end
If you plan to delete rows, overriding _delete_dataset may be a good idea
as well.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
