I'm breaking a bit with my preferred style and including the whole
thread in the posting because it's been nearly a month since I started
the thread. October seems to be meeting and conference month ... and so
here I am on a Sunday morning ...
Mike Rylander put me on the track of mapping the hashes into an ordered
list.
The code evolved to:
if (@ordered_sql_rows) {
for (my $i = 0; $i <= $#ordered_sql_rows; $i++) {
foreach my $key (keys %{$ordered_sql_rows[$i]}) {
print ($key. ": " .$ordered_sql_rows[$i]{$key}. "<br />\n"); }
}
}
At this stage I get all of the values of the first record (the correct
first record according to swish-e), but the remaining values of the list
are blank. It's as though only the first has from $sql_rows got
transferred to @ordered_sql_rows. Is this possible or am I missing some
other step?
Walter
===
Mike Rylander' brilliant reply was:
I don't have too much time to go into a full implementation right now
and I'm not familiar with the *swish-e **Perl* API, but assuming @recs
contains a relevance-ordered set of IDs from *swish-e* and you have done
the batch select using $sth = $dbh->prepare($sql) and $sth->execute()
(using your WHERE ID IN (...)) you can try:
my $sql_rows = $sth->fetchall_hashref('ID');
my @ordered_sql_rows = map { $$sql_rows{$_} } @recs;
Now @ordered_sql_rows will contain a list of hashrefs, one for each
record from the database, ordered by the *swish-e* relevance.
Walter Lewis wrote:
The problem:
Using the perl api of Swish-e returns a reasonably relevance ranked
set of record ids (say 150, 75, 225). But not all of the data for the
result set is stored in the swish indexes.
If I request the records individually from the sql database
afterwards, I end up with a significant delay (~.66 seconds per record).
If I request the records collectively (e. g. WHERE ID IN (150, 75,
225)) the results come back quickly but I don't appear to be force the
order of these records (in the normal course of events, they arrive as
75, 150, 225. Swish has the relevant order; the sql database only has
the data. I tried pre-loading more fields into the swish indexes but
they are essentially flat; the data isn't.
Current thinking:
Get full set of records back from sql using DBI and fetchall_arrayref().
Current problem:
I'm having trouble finding a good working example of the form:
=====
while looping through the swish index results
* find sql result "row" whose "id" equals the swish index "id"
* populate a set of strings with the sql columns from
that specific row
pass string to the appropriate transformation function
(to render Dublin Core, mods etc.)
add to XML content string
}
}
return the XML
=====
and in particular the syntax for the two "lines" that I've put an "*"
beside.
Every example I've seen in _Programming Perl_, _Programming the Perl
DBI_ and the _Cookbook_ tosses off the results of the
fetchall_arrayref() with a foreach loop.
Walter Lewis
Halton Hills
