Complex query

[kaare]

Hi

The last_insert callback worked like a charm :-)

Now, I'd like to ask your opinion about a rather complex query like
this WITH
RECURSIVE thing:

http://blogs.perl.org/users/kaare/2010/05/depth-first-nodetree.html

How would you go about handling this in DBIx::Datamodel?

- DBIx::Datamodel::View ?
- DBIx::Datamodel::Statement ?
- Something else ?
- Not at all ?

--

Med venlig hilsen
Kaare Rasmussen, Jasonic

Jasonic Telefon: +45 3816 2582
Nordre Fasanvej 12
2000 Frederiksberg Email: ka...@jasonic.dk

[dami]

On 31 mai, 21:09, kaare <rasmussen.ka...@gmail.com> wrote:
> Hi
>
> The last_insert callback worked like a charm :-)
>
> Now, I'd like to ask your opinion about a rather complex query like
> this WITH
> RECURSIVE thing:
>
> http://blogs.perl.org/users/kaare/2010/05/depth-first-nodetree.html
>
> How would you go about handling this in DBIx::Datamodel?

According to our blog, you want to generate a query like the
following :

WITH RECURSIVE nodetree(level, id, pid, sort) AS (
SELECT 1, id, parent, '{1}'::int[] FROM nodes WHERE parent IS NULL
UNION
SELECT level+1,p.id, parent, sort||p.id FROM nodetree pr
JOIN nodes p ON p.parent = pr.id
)
SELECT * FROM nodetree ORDER BY sort;

To my knowledge, there is nothing in SQL::Abstract to support WITH
RECURSIVE clauses.
So my advice would be to generate this clause by hand, using a -
postSQL callback.

1) define your 'nodetree' table (assuming your schema is called 'MS')

MS->Table(qw/Nodetree nodetree id/);

2) select

my $rows = MS::Nodetree->select(
-postSQL => sub {my ($sql, @bind) = @_; return ("WITH
RECURSIVE .... $sql", @bind); },
-orderBy => 'sort'
);

3) use $rows as for any regular query.

[kaare]

Hi

> To my knowledge, there is nothing in SQL::Abstract to support WITH
> RECURSIVE clauses.
> So my advice would be to generate this clause by hand, using a -
> postSQL callback.

I'm sure this will work, but it's really a special case where I want
to prepare and execute. So I'm looking for a way to break out into old
fashioned DBI and still have DD to accept the result. It's almost a
View, but not quite.

[laurent dami]

Le 06.06.2010 17:01, kaare a �crit :

If you want fine control on the DBI steps, you can always go

my $statement = $datasource->createStatement;
$statement->refine(...);
$statement->prepare(...);
$statement->execute(...);
while (my $row = $statement->next) {...}

where $datasource can be a Table or a View. In your case, define a View
with your special SQL, and then use a statement as shown above. The doc
is in DBIx::DataModel::Doc::Design/"STATEMENT OBJECTS". If needed you
can access the underlying $dbh handle at the DBI level.

[laurent dami]

Le 07.06.2010 22:26, Kaare Rasmussen a �crit :
> Hi
>
> There seems to be no way to get around the initial select, as execute calls
> sqlize which in turn calls sql abstracts's select.
>
> True?
>
> I concur that the WITH [RECURSIVE] syntax is crazee, but it's what it is :-(
>
> I don't know if it is of interest of you, and if you have access to
> PostgreSQL. If so, I can put together an example that will run in straight
> DBD::Pg.
>
>
OK, here is a proposal.

use strict;
use warnings;
use DBIx::DataModel;

my $with_clause = <<"";

WITH RECURSIVE nodetree(level, id, pid, sort) AS (
SELECT 1, id, parent, '{1}'::int[] FROM nodes WHERE parent IS NULL
UNION
SELECT level+1,p.id, parent, sort||p.id FROM nodetree pr
JOIN nodes p ON p.parent = pr.id
)

DBIx::DataModel->Schema('Tst')
->Table(qw/Nodetree nodetree id/);

my $stmt = Tst::Nodetree->createStatement(
-postSQL => sub {my $sql = shift; $sql =~ s/^/$with_clause/; return
$sql, @_ },
-orderBy => 'sort',
);

$stmt->sqlize;
print join "\n", "SQL IS:", $stmt->sql;

# now you can do : $stmt->prepare; $stmt->execute;