custom result source calling a stored procedure

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

custom result source calling a stored procedure

Mitchell Elutovich
For a customer result source, I know we can do something such as:

$new_source->name( \<<SQL );
  ( SELECT u.* FROM user u 
  INNER JOIN user_friends f ON u.id = f.user_id 
  WHERE f.friend_user_id = ?
  UNION 
  SELECT u.* FROM user u 
  INNER JOIN user_friends f ON u.id = f.friend_user_id 
  WHERE f.user_id = ? )
  SQL

is there any restrictions on the SQL; is it possible to have the SQL just be a call to a stored procedure?

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: custom result source calling a stored procedure

Will Crawford
$users->search (
 [
  { 'user_friends.user_id' => $id },
  { 'user_friends.friend_user_id' => $id }
 ], {
  join => 'user_friends'
 }
)->all



On 10 June 2014 12:18, Mitchell Elutovich <[hidden email]> wrote:

> For a customer result source, I know we can do something such as:
>
> $new_source->name( \<<SQL );
>   ( SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.user_id
>   WHERE f.friend_user_id = ?
>   UNION
>   SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.friend_user_id
>   WHERE f.user_id = ? )
>   SQL
>
> is there any restrictions on the SQL; is it possible to have the SQL just be
> a call to a stored procedure?
>
> _______________________________________________
> 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@...

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: custom result source calling a stored procedure

Mitchell Elutovich
Will misunderstood my question instead of the SELECT ... 

can I do something like?

$new_source->name( \<<SQL );
call mystoredproc(?);
SQL




On Tue, Jun 10, 2014 at 7:59 AM, Will Crawford <[hidden email]> wrote:
$users->search (
 [
  { 'user_friends.user_id' => $id },
  { 'user_friends.friend_user_id' => $id }
 ], {
  join => 'user_friends'
 }
)->all



On 10 June 2014 12:18, Mitchell Elutovich <[hidden email]> wrote:
> For a customer result source, I know we can do something such as:
>
> $new_source->name( \<<SQL );
>   ( SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.user_id
>   WHERE f.friend_user_id = ?
>   UNION
>   SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.friend_user_id
>   WHERE f.user_id = ? )
>   SQL
>
> is there any restrictions on the SQL; is it possible to have the SQL just be
> a call to a stored procedure?
>
> _______________________________________________
> 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@...

_______________________________________________
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@...


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: custom result source calling a stored procedure

Peter Rabbitson-2
In reply to this post by Mitchell Elutovich
On Tue, Jun 10, 2014 at 07:18:29AM -0400, Mitchell Elutovich wrote:

> For a customer result source, I know we can do something such as:
>
> $new_source->name( \<<SQL );
>   ( SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.user_id
>   WHERE f.friend_user_id = ?
>   UNION
>   SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.friend_user_id
>   WHERE f.user_id = ? )
>   SQL
>
> is there any restrictions on the SQL; is it possible to have the SQL just
> be a call to a stored procedure?

Is there a reason you want to put the sproc behind a source? Why not
simply do

my @results = $schema->storage->dbh_do(sub {
  $_[1]->selectall_arrayref(... sproc here ... )
} );

Cheers


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: custom result source calling a stored procedure

Mitchell Elutovich
In the package which has the source I've defined the columns and defined an inflate_result method which blesses $self to a subclass of the package based on a type column

Currently I've just been using a complex SQL of a union of multiple SELECTs.  However, I would like to create a temporary table or two and so I wanted to put everything into a stored proc.


On Tue, Jun 10, 2014 at 2:07 PM, Peter Rabbitson <[hidden email]> wrote:
On Tue, Jun 10, 2014 at 07:18:29AM -0400, Mitchell Elutovich wrote:
> For a customer result source, I know we can do something such as:
>
> $new_source->name( \<<SQL );
>   ( SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.user_id
>   WHERE f.friend_user_id = ?
>   UNION
>   SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.friend_user_id
>   WHERE f.user_id = ? )
>   SQL
>
> is there any restrictions on the SQL; is it possible to have the SQL just
> be a call to a stored procedure?

Is there a reason you want to put the sproc behind a source? Why not
simply do

my @results = $schema->storage->dbh_do(sub {
  $_[1]->selectall_arrayref(... sproc here ... )
} );

Cheers


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: custom result source calling a stored procedure

Peter Rabbitson-2
On Tue, Jun 10, 2014 at 02:38:51PM -0400, Mitchell Elutovich wrote:
> In the package which has the source I've defined the columns and defined an
> inflate_result method which blesses $self to a subclass of the package
> based on a type column
>
> Currently I've just been using a complex SQL of a union of multiple
> SELECTs.  However, I would like to create a temporary table or two and so I
> wanted to put everything into a stored proc.
>

Then yes, just go for it. If it doesn't work - come back and we'll look
into it further.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: custom result source calling a stored procedure

Sheeju Alex
In reply to this post by Mitchell Elutovich
The best way to execute your stored proc is to get the database handle and call stored proc, for more details see here

https://metacpan.org/pod/DBIx%3a%3aClass%3a%3aStorage%3a%3aDBI#dbh_do


Best Regards,    
Sheeju Alex


On Tue, Jun 10, 2014 at 10:13 PM, Mitchell Elutovich <[hidden email]> wrote:
Will misunderstood my question instead of the SELECT ... 

can I do something like?

$new_source->name( \<<SQL );
call mystoredproc(?);
SQL




On Tue, Jun 10, 2014 at 7:59 AM, Will Crawford <[hidden email]> wrote:
$users->search (
 [
  { 'user_friends.user_id' => $id },
  { 'user_friends.friend_user_id' => $id }
 ], {
  join => 'user_friends'
 }
)->all



On 10 June 2014 12:18, Mitchell Elutovich <[hidden email]> wrote:
> For a customer result source, I know we can do something such as:
>
> $new_source->name( \<<SQL );
>   ( SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.user_id
>   WHERE f.friend_user_id = ?
>   UNION
>   SELECT u.* FROM user u
>   INNER JOIN user_friends f ON u.id = f.friend_user_id
>   WHERE f.user_id = ? )
>   SQL
>
> is there any restrictions on the SQL; is it possible to have the SQL just be
> a call to a stored procedure?
>
> _______________________________________________
> 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@...

_______________________________________________
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@...


_______________________________________________
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@...


_______________________________________________
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@...