Re: [Dbix-class] Excluding columns from a query
and BYTEA 2010/2/24 Dan Horne dan.ho...@redbone.co.nz: If coltype == TEXT | BLOB | NTEXT don't select it And CLOB, please ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Tue, Feb 23, 2010 at 12:26 AM, Darren Duncan dar...@darrenduncan.netwrote: fREW Schmidt wrote: And after I write this I am planning on writing a module for DBIx::Class::Helpers that will disable TEXT and BLOBs etc by default :-) And what do you mean by that exactly? Taken literally, it would exclude all character string data, which is very common, and you'd basically be left with numeric and date columns. Perhaps you meant to say CLOBs rather than TEXT? If you mean only long text columns, then how you specify this varies greatly by DBMS, some of which don't have a literal TEXT AFAIK; so what criteria are you using to see if a character string column is short enough to include? What is a reasonable cutoff? Or would you return all character string columns but truncate them to some maximum length suitable for giving a preview? -- Darren Duncan Er, I didn't mean columns with text in them, I meant columns of type TEXT. TEXT != char or varchar. Of course in some DB's they are the same (sqlite) but that's not really the point. Basically my first cut will work like this: If coltype == TEXT | BLOB | NTEXT don't select it Of course that will need to be tailored per db, but I'm in the business of using my code, not perfecting it :-) Point being that if people want it to work with their db which works differently than I'm used to, patches are welcome. The code should be super simple, so helping should be easy. As for your last question, regarding some kind of preview function, there is no way I am doing that. That would just be hassle city for me. If you'd like that functionality I'd be glad to discuss ways to implement it though :-) -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
If coltype == TEXT | BLOB | NTEXT don't select it And CLOB, please ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
fREW Schmidt wrote: And after I write this I am planning on writing a module for DBIx::Class::Helpers that will disable TEXT and BLOBs etc by default :-) And what do you mean by that exactly? Taken literally, it would exclude all character string data, which is very common, and you'd basically be left with numeric and date columns. Perhaps you meant to say CLOBs rather than TEXT? If you mean only long text columns, then how you specify this varies greatly by DBMS, some of which don't have a literal TEXT AFAIK; so what criteria are you using to see if a character string column is short enough to include? What is a reasonable cutoff? Or would you return all character string columns but truncate them to some maximum length suitable for giving a preview? -- Darren Duncan ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On 18.02.2010 20:40, fREW Schmidt wrote: And after I write this I am planning on writing a module for DBIx::Class::Helpers that will disable TEXT and BLOBs etc by default :-) This would be really useful and solve my original problem even more elegantly. Nick ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Thu, Feb 18, 2010 at 7:02 AM, BUCHMULLER Norbert norbi.li...@nix.huwrote:
On Wed, 17 Feb 2010 09:19:44 -0600 fREW Schmidt wrote:
1. Only have the remove-columns affect the default select, since
having it affect an explicit columns list is a little silly.
2. Having it affect the *current* select list; the use-case for this
would be if you had an explicit columns list in previous search (or a
predefined search more likely) and you wanted to take away a column
from that.
I'd vote for #2 as it would make possible things like that (a variant of
the use-case you mentioned):
$rs-method_that_adds_a_calculated_column-search(
...
{
remove_columns = ['big_text_column'],
}
); # and still have the calculated column (added via +columns)
Also this behaviour is the less surprising: with #1 the behaviour of
'remove_columns' (or '-columns' or whatever it will be called)
would depend on whether a given column comes from the result source or
from a previous search() call.
norbi
ok, I've heard from I think 4 people regarding this now and everyone is in
favor of #2. I'll implement it soon (within a week hopefully.)
--
fREW Schmidt
http://blog.afoolishmanifesto.com
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
From: fREW Schmidt fri...@gmail.com
On Thu, Feb 18, 2010 at 7:02 AM, BUCHMULLER Norbert
norbi.li...@nix.huwrote:
On Wed, 17 Feb 2010 09:19:44 -0600 fREW Schmidt wrote:
1. Only have the remove-columns affect the default select, since
having it affect an explicit columns list is a little silly.
2. Having it affect the *current* select list; the use-case for this
would be if you had an explicit columns list in previous search (or
a
predefined search more likely) and you wanted to take away a column
from that.
I'd vote for #2 as it would make possible things like that (a variant of
the use-case you mentioned):
$rs-method_that_adds_a_calculated_column-search(
...
{
remove_columns = ['big_text_column'],
}
); # and still have the calculated column (added via +columns)
Also this behaviour is the less surprising: with #1 the behaviour of
'remove_columns' (or '-columns' or whatever it will be called)
would depend on whether a given column comes from the result source or
from a previous search() call.
norbi
ok, I've heard from I think 4 people regarding this now and everyone is in
favor of #2. I'll implement it soon (within a week hopefully.)
--
fREW Schmidt
http://blog.afoolishmanifesto.com
Would it be possible to specify columns from joined tables like in the line
below?
remove_columns = ['big_text_column',
'another_table.another_big_column'],
It would be great!
Octavian
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Thu, Feb 18, 2010 at 12:58 PM, Octavian Rasnita octav...@fcc.ro wrote:
From: fREW Schmidt fri...@gmail.com
On Thu, Feb 18, 2010 at 7:02 AM, BUCHMULLER Norbert norbi.li...@nix.hu
wrote:
On Wed, 17 Feb 2010 09:19:44 -0600 fREW Schmidt wrote:
1. Only have the remove-columns affect the default select, since
having it affect an explicit columns list is a little silly.
2. Having it affect the *current* select list; the use-case for this
would be if you had an explicit columns list in previous search (or
a
predefined search more likely) and you wanted to take away a column
from that.
I'd vote for #2 as it would make possible things like that (a variant of
the use-case you mentioned):
$rs-method_that_adds_a_calculated_column-search(
...
{
remove_columns = ['big_text_column'],
}
); # and still have the calculated column (added via +columns)
Also this behaviour is the less surprising: with #1 the behaviour of
'remove_columns' (or '-columns' or whatever it will be called)
would depend on whether a given column comes from the result source or
from a previous search() call.
norbi
ok, I've heard from I think 4 people regarding this now and everyone is in
favor of #2. I'll implement it soon (within a week hopefully.)
Would it be possible to specify columns from joined tables like in the line
below?
remove_columns = ['big_text_column',
'another_table.another_big_column'],
It would be great!
Uh, I haven't looked at that specifically, but I think it would be harder
*not* to do that since I'd need to special case it in the code.
And after I write this I am planning on writing a module for
DBIx::Class::Helpers that will disable TEXT and BLOBs etc by default :-)
--
fREW Schmidt
http://blog.afoolishmanifesto.com
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
Gah, I should have read this thread sooner! I am actually working on adding such a feature to DBIC. Getting it to work is quite easy. Getting the right semantics is hard. For instance, we've discussed this on IRC and really there are two options: 1. Only have the remove-columns affect the default select, since having it affect an explicit columns list is a little silly. 2. Having it affect the *current* select list; the use-case for this would be if you had an explicit columns list in previous search (or a predefined search more likely) and you wanted to take away a column from that. I'm personally in favor of the former, mostly because it's the simplest to explain, test, and implement, and really all this is is a shortcut anyway. Honestly I've implemented this twice now, both ways, but we need to nail down the semantics. Furthermore, I feel like implementing remove-select and remove-as is needlessly complex. If someone provides a select/as just blow it away with columns. What do you guys think? -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Wed, Feb 17, 2010 at 9:19 AM, fREW Schmidt fri...@gmail.com wrote: Gah, I should have read this thread sooner! I am actually working on adding such a feature to DBIC. Getting it to work is quite easy. Getting the right semantics is hard. For instance, we've discussed this on IRC and really there are two options: 1. Only have the remove-columns affect the default select, since having it affect an explicit columns list is a little silly. What if a query is built by getting the columns to select from one source and the columns to exclude from another? Say the client says I want X Y and Z and the security layer says We don't send Z over non-encrypted channels. The resulting select would look silly, granted, but it might be easier in some situations for Thing A to contribute includes and Thing B to contribute excludes without A and B needing to communicate with/know about each other. (Also I guess that kind of assumes that exclude wins when a column is both included and excluded, but that's sort of true for both methods.) mike 1. Having it affect the *current* select list; the use-case for this would be if you had an explicit columns list in previous search (or a predefined search more likely) and you wanted to take away a column from that. I'm personally in favor of the former, mostly because it's the simplest to explain, test, and implement, and really all this is is a shortcut anyway. Honestly I've implemented this twice now, both ways, but we need to nail down the semantics. Furthermore, I feel like implementing remove-select and remove-as is needlessly complex. If someone provides a select/as just blow it away with columns. What do you guys think? -- fREW Schmidt http://blog.afoolishmanifesto.com ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
fREW Schmidt wrote: Gah, I should have read this thread sooner! I am actually working on adding such a feature to DBIC. Getting it to work is quite easy. Getting the right semantics is hard. For instance, we've discussed this on IRC and really there are two options: 1. Only have the remove-columns affect the default select, since having it affect an explicit columns list is a little silly. 2. Having it affect the *current* select list; the use-case for this would be if you had an explicit columns list in previous search (or a predefined search more likely) and you wanted to take away a column from that. I'm personally in favor of the former, mostly because it's the simplest to explain, test, and implement, and really all this is is a shortcut anyway. Honestly I've implemented this twice now, both ways, but we need to nail down the semantics. Furthermore, I feel like implementing remove-select and remove-as is needlessly complex. If someone provides a select/as just blow it away with columns. What do you guys think? I consider #2 to be a more complete solution, and #1 to be a trivial case of #2, albeit the most important one. For syntax, I would just support remove-columns in exactly the same way as the select-list, or integrate those 2 things into a single query part, which effectively they are. The most elegant solution I see, conceptually at least, is to treat everything except the remove-columns as if it were a plain subquery/view/table and then the semantics are to return everything that does by default except what remove-columns lists. That is, exactly the same semantics as a normal select-columns except you return the complementary column set. And so, when you are doing query chaining such as a DBIC feature, the result of each prior item in the chain is effectively a source subquery/view/table of the next part, in which case the semantics for the new feature should be obvious. More broadly speaking, one should be able conceivably to take any monolithic SELECT and break it down into a chain of distinct operations, eg the join and where and group and select-list (projection) would each be a new query in the chain based off the result of the previous operation; the order that the parts are in the chain can be variable to some extent, those operations being logically associative (except when duplicates or nulls are involved). That's how I see this anyway. -- Darren Duncan ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
From: Robert Sedlacek r...@474.at Hey Nick, Nick Wellnhofer wrote: It's simply an optimization. I have a table with a text column that can contain tens of KB of data per row. I also have some queries that don't need that column, so I'd like to avoid unnecessarily fetching all that content from the DB. Depending on the queries you need to perform you might get away with separating the larger content out into a separate result source that you prefetch only when required. regards, -- Robert 'phaylon' Sedlacek Do I understand correctly that it is possible to create a Result class with fewer columns, give it another name but make it use the same table, and use it in relations as the original Result class? Is it possible to subclass a Result class and just specify that we want to skip some columns from it? If yes, is there a method for deleting those columns? Thanks. Octavian ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On 15/02/10 18:01, Robert Sedlacek wrote: Hey Nick, Nick Wellnhofer wrote: It's simply an optimization. I have a table with a text column that can contain tens of KB of data per row. I also have some queries that don't need that column, so I'd like to avoid unnecessarily fetching all that content from the DB. Depending on the queries you need to perform you might get away with separating the larger content out into a separate result source that you prefetch only when required. Yes, I thought about that. It's probably what I'm going to do. Nick ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Fri, Feb 12, 2010 at 9:06 PM, Darren Duncan dar...@darrenduncan.netwrote:
Rob Kinyon wrote:
On Fri, Feb 12, 2010 at 09:35, Nick Wellnhofer wellnho...@aevum.de
wrote:
On 12.02.2010 15:17, Rob Kinyon wrote:
What is the *USE CASE* that is leading you to think that excluding
columns from a -search is a good idea?
It's simply an optimization. I have a table with a text column that can
contain tens of KB of data per row. I also have some queries that don't
need that column, so I'd like to avoid unnecessarily fetching all that
content from the DB.
Ah. Now, had you said this at the beginning, you would have received
an answer yesterday. http://www.google.com/?q=XY+Problem
There is currently no clean way to do this. The best way I can think
of would be something like:
1) Don't specify it as a column in your resultsource.
2) when you need it, add it in the columns= attribute and use
get_column('x')
It's not very simple right now. Ideally, there would be a
fetch_lazily column attribute. Patches welcome. :)
Logically, being able to specify directly what you don't want is a good
idea simply for means of completeness and balance.
In any situation where you can specify a condition, it is reasonable to be
able to specify the complementary or opposite condition with the same level
of verbosity.
With Perl's grep or SQL's where/having, specifying a complement is as
simple as prepending not to an otherwise identical condition. Both of
these operations are normally picking a subset of a given set, and it is
reasonable to request the complementary subset.
And so, if you can say select column-name-list from foo then one should
also be able to say select all but column-name-list from foo. Arguing
that this shouldn't be the case is like arguing that not or ! shouldn't
be supported.
This is something simple that SQL should support natively, and if it
doesn't then that's a failing of SQL.
hear, hear. SQL doesn't have it afaik--its failure to have it is exactly
that. I have wanted it many, many times. From a command line client,
wanting everything but that one column that blows out the display, etc. I
think the fact that the failure to have this is so widespread comes from
SQL's failure to have it.
I am not a real computer scientist--maybe there is some good reason for this
to be unavailable in SQL? But as far as use cases go, I think this would
obviously be useful. A column with a large amount of data, a column with
sensitive data, a column that should be read only, any set of columns that
are always going to be there but never going to be wanted--you can code it
all but X and if 'all' changes your code is still pulling the correct set.
In other words, I think it would be in the category of obviously useful,
and maybe something that hasn't been asked for a lot because it's also in
the category of inexplicably missing from SQL, and people have just become
accustomed to coding around its absence.
mike
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On 12.02.2010 04:49, Rob Kinyon wrote:
On Thu, Feb 11, 2010 at 17:24, Darren Duncan dar...@darrenduncan.net wrote:
Nick Wellnhofer wrote:
If I want to exclude a single column from a query I currently have to list
all columns except the one to be excluded in the 'columns' attribute. This
is cumbersone and AFAICS doesn't work with prefetch. It would be nice if I
could simply pass a list of all the columns I want to be excluded including
prefetched ones.
Or am I missing something?
I don't know if you can do that, but it is certainly a very reasonable thing
to want to do. -- Darren Duncan
my $rs2 = $rs-search(
undef,
{
columns = [ grep { $_ ne 'exclude_me' } $rs-resultsource-columns ]
}
);
Thanks for the tip. I keep forgetting about the usefulness of the grep
function. But that doesn't work with prefetch. I think something like
this would be handy:
$rs-search(
undef,
{
prefetch = 'related',
exclude = [ 'me.exclude_me', 'related.exclude_me_too' ],
}
);
You can also exclude them at the ResultSet level - I leave finding out
how as an exercise for the reader.
What do you mean? Overriding the search method?
Nick
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On 12.02.2010 15:17, Rob Kinyon wrote: What is the *USE CASE* that is leading you to think that excluding columns from a -search is a good idea? It's simply an optimization. I have a table with a text column that can contain tens of KB of data per row. I also have some queries that don't need that column, so I'd like to avoid unnecessarily fetching all that content from the DB. Nick ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Fri, Feb 12, 2010 at 09:35, Nick Wellnhofer wellnho...@aevum.de wrote:
On 12.02.2010 15:17, Rob Kinyon wrote:
What is the *USE CASE* that is leading you to think that excluding
columns from a -search is a good idea?
It's simply an optimization. I have a table with a text column that can
contain tens of KB of data per row. I also have some queries that don't
need that column, so I'd like to avoid unnecessarily fetching all that
content from the DB.
Ah. Now, had you said this at the beginning, you would have received
an answer yesterday. http://www.google.com/?q=XY+Problem
There is currently no clean way to do this. The best way I can think
of would be something like:
1) Don't specify it as a column in your resultsource.
2) when you need it, add it in the columns= attribute and use get_column('x')
It's not very simple right now. Ideally, there would be a
fetch_lazily column attribute. Patches welcome. :)
Rob
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
Excerpts from Rob Kinyon's message of Fri Feb 12 10:01:38 -0500 2010: Ah. Now, had you said this at the beginning, you would have received an answer yesterday. http://www.google.com/?q=XY+Problem 404 :) hdp. ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
I think -select and -as make sense
Justin
On Feb 12, 2010 5:18 AM, Nick Wellnhofer wellnho...@aevum.de wrote:
On 12.02.2010 04:49, Rob Kinyon wrote:
On Thu, Feb 11, 2010 at 17:24, Darren Duncan dar...@darren...
Thanks for the tip. I keep forgetting about the usefulness of the grep
function. But that doesn't work with prefetch. I think something like
this would be handy:
$rs-search(
undef,
{
prefetch = 'related',
exclude = [ 'me.exclude_me', 'related.exclude_me_too' ],
}
);
You can also exclude them at the ResultSet level - I leave finding out
how as an exerc...
What do you mean? Overriding the search method?
Nick
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/list...
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
Rob Kinyon wrote:
On Fri, Feb 12, 2010 at 09:35, Nick Wellnhofer wellnho...@aevum.de wrote:
On 12.02.2010 15:17, Rob Kinyon wrote:
What is the *USE CASE* that is leading you to think that excluding
columns from a -search is a good idea?
It's simply an optimization. I have a table with a text column that can
contain tens of KB of data per row. I also have some queries that don't
need that column, so I'd like to avoid unnecessarily fetching all that
content from the DB.
Ah. Now, had you said this at the beginning, you would have received
an answer yesterday. http://www.google.com/?q=XY+Problem
There is currently no clean way to do this. The best way I can think
of would be something like:
1) Don't specify it as a column in your resultsource.
2) when you need it, add it in the columns= attribute and use get_column('x')
It's not very simple right now. Ideally, there would be a
fetch_lazily column attribute. Patches welcome. :)
Logically, being able to specify directly what you don't want is a good idea
simply for means of completeness and balance.
In any situation where you can specify a condition, it is reasonable to be able
to specify the complementary or opposite condition with the same level of verbosity.
With Perl's grep or SQL's where/having, specifying a complement is as
simple as prepending not to an otherwise identical condition. Both of these
operations are normally picking a subset of a given set, and it is reasonable to
request the complementary subset.
And so, if you can say select column-name-list from foo then one should also
be able to say select all but column-name-list from foo. Arguing that this
shouldn't be the case is like arguing that not or ! shouldn't be supported.
This is something simple that SQL should support natively, and if it doesn't
then that's a failing of SQL. (And individual DBMSs can still buck the trend by
supporting that even if the SQL standard doesn't, considering they all have
their own extensions anyway and this one is backwards compatible.) DBIx::Class
should support it natively regardless of whether SQL does just because it is
good design and keeps the interface more balanced, and there are reasonable use
cases for it. As for how that is implemented, well the process would be related
to how DBIx::Class supports select * from foo; to know what result columns you
will have, you consult metadata you have about the structure of foo, either that
you already hold or you ask the DBMS.
-- Darren Duncan
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
Nick Wellnhofer wrote: If I want to exclude a single column from a query I currently have to list all columns except the one to be excluded in the 'columns' attribute. This is cumbersone and AFAICS doesn't work with prefetch. It would be nice if I could simply pass a list of all the columns I want to be excluded including prefetched ones. Or am I missing something? I don't know if you can do that, but it is certainly a very reasonable thing to want to do. -- Darren Duncan ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Excluding columns from a query
On Thu, Feb 11, 2010 at 17:24, Darren Duncan dar...@darrenduncan.net wrote:
Nick Wellnhofer wrote:
If I want to exclude a single column from a query I currently have to list
all columns except the one to be excluded in the 'columns' attribute. This
is cumbersone and AFAICS doesn't work with prefetch. It would be nice if I
could simply pass a list of all the columns I want to be excluded including
prefetched ones.
Or am I missing something?
I don't know if you can do that, but it is certainly a very reasonable thing
to want to do. -- Darren Duncan
my $rs2 = $rs-search(
undef,
{
columns = [ grep { $_ ne 'exclude_me' } $rs-resultsource-columns ]
}
);
You can also exclude them at the ResultSet level - I leave finding out
how as an exercise for the reader.
Rob
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
