Comments (21)
frioux
commented on June 29, 2024
1
Fixed by #98
from dbix-class-helpers.
ribasushi
commented on June 29, 2024
1
@karenetheridge indeed, I screwed up an aliasing step when writing up the patch for @frioux. Something like this should do it, but I will not have time to properly test/PR this:
--- lib/DBIx/Class/Helper/ResultSet/Shortcut/ResultsExist.pm.orig 2019-12-09 19:44:10.666520779 +0100
+++ lib/DBIx/Class/Helper/ResultSet/Shortcut/ResultsExist.pm 2019-12-09 19:47:21.834153731 +0100
@@ -24,11 +24,14 @@
sub results_exist {
my $self = shift;
+ my $inner_q = $self->results_exist_as_query;
+ $$inner_q->[0] .= " AS _existence_subq";
+
my( undef, $sth ) = $self->result_source
->schema
->storage
->_select(
- $self->results_exist_as_query,
+ $inner_q,
\'*',
{},
{},
from dbix-class-helpers.
frioux
commented on June 29, 2024
cc: @oalders
from dbix-class-helpers.
oalders
commented on June 29, 2024
[13:02:19] <@frew> our best bet at this point is to add a limit => 1 or do research on if there is a standard dummy empty table
[13:02:26] <@frew> or something like that
[13:02:39] <@frew> I think the end result needs to be like
[13:02:50] <@frew> SELECT 1 FROM DUMMY where EXISTENCE CHECK
[13:02:53] <@frew> more or less
[13:03:07] <@frew> and actually dummy should have 1 row
from dbix-class-helpers.
frioux
commented on June 29, 2024
Seemingly unrelated conversation that could resolve our issue:
14:56:44 xinming_ | mst: Is it possible to make DBIC to do soemthing like SELECT 1 FROM table WHERE ...?
14:57:10 @mst | step back. explain what you're trying to achieve.
14:57:18 xinming_ | What I mean is something like SELECT EXISTS (SELECT ... FROM ...);
14:57:39 xinming_ | I want to test wether we have results in for a ResultSet
14:58:25 xinming_ | so we don't need to use count to access all rows. Since in postgres, It'll try to check all rows matched.
14:59:10 xinming_ | so something like 'SELECT 1 FROM table WHERE rs filtering clauses'
15:01:06 @mst | columns => [ { foo => \1 } ] ?
15:02:06 xinming_ | $self->search($query, { rows => 1, columns => [\1] })->single; <--- Something like this?
15:02:18 xinming_ | Let me ry.
15:02:19 xinming_ | try
15:06:55 tharkun | How can I do an INSERT ... SELECT ... with DBIx::Class ? man page will suffice
15:07:54 @castaway | tharkun: ->as_query
15:08:10 tharkun | castaway: Thanks
15:09:39 xinming_ | Thanks
15:09:41 xinming_ | got it
15:10:12 xinming_ | $rs->search(undef, { columns => { exists => \1 }, rows => 1 })->single->get_column('exists'); worked
from dbix-class-helpers.
frioux
commented on June 29, 2024
Got started on this and sadly the answer @shadowcat-mst gave to @xinming fails tests and always returns true.
from dbix-class-helpers.
frioux
commented on June 29, 2024
Oh the branch I started on was here if anyone wants to try out themselves.
from dbix-class-helpers.
ilmari
commented on June 29, 2024
The simplest thing would be $self->search(undef, { rows => 1 })->count, but this bit in DBIx::Class::ResultSet::Count defeats that:
# this is a little optimization - it is faster to do the limit
# adjustments in software, instead of a subquery
my ($rows, $offset) = delete @{$attrs}{qw/rows offset/};It might be the case for some databases without built-in LIMIT (or equivalent) and have to do horrible ROW_COUNT subqueries, but PostgreSQL definitely benefits from leaving the LIMIT in.
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
Has anyone looked at this in a while? it would be great to not have a dangling cursor from the ->first in the current implementation on cpan.
from dbix-class-helpers.
frioux
commented on June 29, 2024
Nope; help welcome :)
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
@ribasushi's comments here (8570231) seemed sane, but this is way too far out of my depth to be able to add on to that.
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
FWIW, here is a postgres-specific implementation:
# generates: select exists (SELECT 1 FROM $table WHERE ... )
sub exists {
my $self = shift;
my $inner = $self->search(undef, { select => [ \1 ] })->as_query;
my $statement = 'select exists ' . $inner->$*->[0];
my @binds = map { $_->[1] } $inner->$*->@[1 .. $inner->$*->$#*];
my ($exists) = $self->result_source->schema->storage->dbh_do(sub {
my ($storage, $dbh) = @_;
# cribbed from DBIx::Class::Storage::DBI::_format_for_trace
$storage->debugobj->query_start($statement, map { defined($_) ? qq{'$_'} : q{NULL} } @binds)
if $storage->debug;
return $dbh->selectrow_array($statement, undef, @binds);
});
return $exists;
}
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
Given the changes to the query that I see in https://gist.github.com/karenetheridge/750f774d1e4dea2692d80d9babecb568, I think I will stick with the original implementation I had. The addition of all columns in the original source, twice, can become quite inefficient.
from dbix-class-helpers.
ribasushi
commented on June 29, 2024
@karenetheridge something locally in your codebase ( some other extension? ) is interfering with the generation of the column list. You need to debug this. The shipped version does not do what you are describing:
rabbit@Ahasver:~$ cpanm --look DBIx::Class::Helpers
--> Working on DBIx::Class::Helpers
Fetching http://www.cpan.org/authors/id/F/FR/FREW/DBIx-Class-Helpers-2.034001.tar.gz ... OK
Entering /home/rabbit/.cpanm/work/1575914802.6172/DBIx-Class-Helpers-2.034001 with /bin/bash
rabbit@Ahasver:~/.cpanm/work/1575914802.6172/DBIx-Class-Helpers-2.034001$ DBIC_TRACE=1 prove -l t/ResultSet/Shortcut/ResultsExist.t
t/ResultSet/Shortcut/ResultsExist.t ..
...
SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM "Foo" "me" WHERE ( "id" > ? )) ): '0'
t/ResultSet/Shortcut/ResultsExist.t .. 1/? SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM "Foo" "me" WHERE ( "id" < ? )) ): '0'
SELECT ( SELECT EXISTS (SELECT 42 FROM "Foo" "correlation" WHERE ( ( "correlation"."id" > "me"."id" AND "id" > ? ) )) ), ( SELECT EXISTS (SELECT 42 FROM "Foo" "correlation" WHERE ( ( "correlation"."id" < "me"."id" AND "id" > ? ) )) ), "me"."id" FROM "Foo" "me" WHERE ( "id" > ? ) ORDER BY "id": '0', '0', '0'
t/ResultSet/Shortcut/ResultsExist.t .. ok
All tests successful.
Files=1, Tests=3, 0 wallclock secs ( 0.02 usr 0.00 sys + 0.56 cusr 0.01 csys = 0.59 CPU)
Result: PASS
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
@ribasushi much to my embarrassment I discovered that I was testing on a machine where I hadn't updated ::Helpers to 2.034001, which contain the reworded exists. :/
However, this leads to a worse problem.. the reworked query doesn't work at all. :(
(This is with PostgreSQL 10.10 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version 9.1.0 (clang-902.0.39.2), 64-bit)
my code:
my $exists = $schema->resultset('build')->search({ name => 'my first build' })->results_exist;
output, with DBIC_TRACE enabled:
SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM build me WHERE ( name = ? )) ): 'my first build'
DBI Exception: DBD::Pg::st execute failed: ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM build me WHERE...
^
HINT: For example, FROM (SELECT ...) [AS] foo. [for Statement "SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM build me WHERE ( name = ? )) )" with ParamValues: 1='my first build'] at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Schema.pm line 1118.
DBIx::Class::Schema::throw_exception(Conch::DB=HASH(0x7fc41b349a28), "DBI Exception: DBD::Pg::st execute failed: ERROR: subquery i"...) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage.pm line 113
DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::Pg=HASH(0x7fc41b348e40), "DBI Exception: DBD::Pg::st execute failed: ERROR: subquery i"...) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/DBI.pm line 1501
DBIx::Class::Storage::DBI::__ANON__("DBD::Pg::st execute failed: ERROR: subquery in FROM must hav"..., DBI::st=HASH(0x7fc41bc53e50), undef) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/DBI.pm line 1836
DBIx::Class::Storage::DBI::_dbh_execute(DBIx::Class::Storage::DBI::Pg=HASH(0x7fc41b348e40), DBI::db=HASH(0x7fc41e4af7b0), "SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM build me WHERE "..., ARRAY(0x7fc41b349ab8), ARRAY(0x7fc41bd15d70)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/DBI.pm line 856
DBIx::Class::Storage::DBI::__ANON__() called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 130
DBIx::Class::Storage::BlockRunner::try {...} () called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/Try/Tiny.pm line 98
eval {...} called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/Try/Tiny.pm line 93
Try::Tiny::try(CODE(0x7fc41bc54150), Try::Tiny::Catch=REF(0x7fc41bd2f610)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 134
DBIx::Class::Storage::BlockRunner::__ANON__() called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/Context/Preserve.pm line 33
Context::Preserve::preserve_context(CODE(0x7fc41d617918), "replace", CODE(0x7fc41f1d0038)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 213
DBIx::Class::Storage::BlockRunner::_run(DBIx::Class::Storage::BlockRunner=HASH(0x7fc41e423ed8), CODE(0x7fc41e4aed60)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 105
DBIx::Class::Storage::BlockRunner::run(DBIx::Class::Storage::BlockRunner=HASH(0x7fc41e423ed8), CODE(0x7fc41e4aed60)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/DBI.pm line 857
DBIx::Class::Storage::DBI::dbh_do(undef, undef, "SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM build me WHERE "..., ARRAY(0x7fc41b349ab8), ARRAY(0x7fc41bd15d70)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/DBI.pm line 1817
DBIx::Class::Storage::DBI::_execute(DBIx::Class::Storage::DBI::Pg=HASH(0x7fc41b348e40), "select", REF(0x7fc41bd0e320), SCALAR(0x7fc41e356be0), HASH(0x7fc41e4b0d88), HASH(0x7fc41f19e9b0)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Storage/DBI.pm line 2409
DBIx::Class::Storage::DBI::_select(DBIx::Class::Storage::DBI::Pg=HASH(0x7fc41b348e40), REF(0x7fc41bd0e320), SCALAR(0x7fc41e356be0), HASH(0x7fc41e4b0d88), HASH(0x7fc41b349110)) called at /Users/ether/.perlbrew/libs/26.3@std/lib/perl5/DBIx/Class/Helper/ResultSet/Shortcut/ResultsExist.pm line 27
DBIx::Class::Helper::ResultSet::Shortcut::ResultsExist::results_exist(Conch::DB::ResultSet::Build=HASH(0x7fc41f190a50)) called at lib/Conch/DB/Helper/ResultSet/ResultsExist.pm line 39
Conch::DB::Helper::ResultSet::ResultsExist::exists(Conch::DB::ResultSet::Build=HASH(0x7fc41f190a50)) called at t/resultsexist.t line 14
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
That appears to work -- the new query is SELECT * FROM ( SELECT EXISTS (SELECT 42 FROM build me WHERE ( name = ? )) ) AS _existence_subq: 'my first build' which successfully evaluates.
I can PR this up for @frioux, but I am also unsure of the ramifications to other db drivers.
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
for my internal use, I've boiled this down to:
sub exists ($self) {
my $inner_q = $self->search(undef, { select => [ \1 ] })->as_query;
$$inner_q->[0] = '( select exists '.$$inner_q->[0].' ) as _existence_subq';
my (undef, $sth) = $self->result_source
->schema
->storage
->_select(
$inner_q,
\'*',
{},
{},
);
return $sth->fetchall_arrayref->[0][0];
}
from dbix-class-helpers.
ribasushi
commented on June 29, 2024
much to my embarrassment I discovered that I was testing on a machine where I hadn't updated
@karenetheridge apparently you also have not looked at the open bugreports 😒
#99
from dbix-class-helpers.
karenetheridge
commented on June 29, 2024
much to my embarrassment I discovered that I was testing on a machine where I hadn't updated
@karenetheridge apparently you also have not looked at the open bugreports 😒
#99
You didn't seem to know about the bug report before yesterday either :)
from dbix-class-helpers.
abraxxa
commented on June 29, 2024
This actually broke results_exist on Oracle:
ORA-00936: missing expression (DBD ERROR: error possibly near <*> indicator at char 23 in 'SELECT * FROM ( SELECT <*>EXISTS (SELECT 42 FROM (
from dbix-class-helpers.
abraxxa
commented on June 29, 2024
https://stackoverflow.com/questions/16562162/how-to-use-select-exists-in-oracle
from dbix-class-helpers.
Related Issues (20)
- Version 2.034001 broke results_exist on Oracle HOT 8
- AutoRemoveColumns _should_column_fetch example incorrect HOT 6
- SelfResultSet returns a Result instead of a ResultSet in list context
- A tad cleaner ProxyResultMethod HOT 1
- Add StrictMode helper for columns
- [PATCH] spelling fixes HOT 1
- [PATCH] Fix a strictness error in a deprecated module HOT 1
- Hella Fast JSON Encoder
- Use DBIx::RetryConnect in tests to remove sleep in with-dbs HOT 1
- Make dont_serialize in Helper::Row::ToJSON a package global? HOT 6
- Problem installing on debian Stretch HOT 4
- Simply loading AutoRemoveColumns seems to interfere with DBICs group_by inferrence HOT 7
- SetOperations UNION needs extra brackets (PostgreSQL)? HOT 4
- Booleans with Helper::Row::ToJSON HOT 5
- AutoRemoveColumns doesn't work with prefetch HOT 1
- AutoRemoveColumns breaks `select =>` HOT 2
- Q: Why `Helper::Row::SelfResultSet` disables `InflateColumn::DateTime` if used first? HOT 2
- DateMethods1 returns week number for day_of_week under MSSQL
- OnColumnMissing breaks create with related objects
- It's swallowing the "-as" that's not for function HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dbix-class-helpers.