[Dbix-class] Excluding columns from a query
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? 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
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 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
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 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
>> 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' ], > } > ); It -shouldn't- work with prefetch as prefetch is just an optimization. You also still haven't mentioned -WHY- you want this. 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
On 12.02.2010 14:40, Rob Kinyon wrote: >>> 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' ], >>} >> ); > > It -shouldn't- work with prefetch as prefetch is just an optimization. > > You also still haven't mentioned -WHY- you want this. I think I did. It's because I'm lazy and being able to do something like my example above would save me from writing more code to achieve the same thing. I understand that this would change the contents of prefetched rows compared to non-prefetched rows, so it's possibly a bad idea. 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:12, Nick Wellnhofer wrote: > I think I did. It's because I'm lazy and being able to do something like > my example above would save me from writing more code to achieve the > same thing. > > I understand that this would change the contents of prefetched rows > compared to non-prefetched rows, so it's possibly a bad idea. What is the *USE CASE* that is leading you to think that excluding columns from a ->search is a good idea? 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
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 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
http://www.google.com/search?q=XY+Problem That used to work. :/ On Fri, Feb 12, 2010 at 10:16, Hans Dieter Pearcey wrote: > 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 > -- Thanks, Rob Kinyon ___ 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" wrote: On 12.02.2010 04:49, Rob Kinyon wrote: > On Thu, Feb 11, 2010 at 17:24, Darren Duncan 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 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 from foo" then one should also be able to say "select all but 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
On Fri, Feb 12, 2010 at 9:06 PM, Darren Duncan wrote: > Rob Kinyon wrote: > >> On Fri, Feb 12, 2010 at 09:35, Nick Wellnhofer >> 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 from foo" then one should > also be able to say "select all but 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
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 Perl 5 Consultant for Shadowcat Systems Limited - http://shadowcat.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
From: "Robert Sedlacek" 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
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 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
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 ___ 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 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.) -- 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" On Thu, Feb 18, 2010 at 7:02 AM, BUCHMULLER Norbert 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.) -- 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 wrote: > From: "fREW Schmidt" > > On Thu, Feb 18, 2010 at 7:02 AM, BUCHMULLER Norbert > >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
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
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 Tue, Feb 23, 2010 at 12:26 AM, Darren Duncan wrote: > 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
and BYTEA 2010/2/24 Dan Horne : > >> >> 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