Hello Florent,
thank you for your answer, here are the SQL Queries you requested. Did you have
a look on my implementation as well? (the pastebin links i sent?) Is there an
error with my implementation?:
thank you,
best
SELECT "_C1" FROM (SELECT "hierarchy"."id" AS "_C1" FROM "hierarchy"
JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id"
JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id", TO_TSQUERY('spanish',
$1) _nxquery, TO_TSQUERY('spanish', $2) _nxquery2 WHERE
"hierarchy"."primarytype"
IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
$19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34,
$35, $36, $37, $38, $39, $40) AND ((_nxquery @@ "fulltext"."fulltext")) AND
((_nxquery2 @@ "fulltext"."fulltext")) UNION ALL SELECT "_H"."id" AS "_C1" FROM
"hie
rarchy" "_H"
JOIN "proxies" ON "_H"."id" = "proxies"."id"
JOIN "hierarchy" ON "hierarchy"."id" = "proxies"."targetid"
JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id"
JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id", TO_TSQUERY('spanish',
$41) _nxquery, TO_TSQUERY('spanish', $42) _nxquery2 WHERE "hierarchy"
."primarytype" IN ($43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54,
$55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70,
$71, $72, $73, $74, $75, $76, $77, $78, $79, $80) AND ((_nxquery @@
"fulltext"."fulltext")) AND ((_nxquery2 @@ "fulltext"."fulltext"))) AS "_T"
As you can see there are two times two "fulltext" JOINS (I broke up the queries
at the joints).
Here the TRACE output from the logs (is this the NXQL Query you asked about? If
not, I found nothing else related in the logs, do I have to boost logging
more?):
SELECT "_C1" FROM (SELECT "hierarchy"."id" AS "_C1" FROM "h
ierarchy" JOIN "fulltext" ON "fulltext"."id" = "hierarchy"."id" JOIN "fulltext"
ON "fulltext"."id" = "hierarchy"."id", TO_TSQUERY('spanish', 'Auto') _nxquery,
TO_TSQUERY('spanish', 'Haus') _nxquery2 WHERE "hierarchy"."primarytype" IN
('TaskLists', 'Digitalization', 'SectionRoot', 'Univers', 'MailMessage',
'Workspace', 'Forum', 'ContextualLink', 'PictureBook', 'BlogSite', 'Post',
'Tag', 'Thread', 'search_results', 'Space', 'Picture', 'UserWorkspacesRoot',
'TemplateRoot', 'Document', 'Folder', 'AdvancedSearchCustom', 'Domain', 'File',
'WorkspaceRoot', 'Comment', 'AdvancedSearch', 'WebSite', 'Note', 'Gadget'
, 'HiddenFolder', 'QueryNav', 'Section', 'TaskList', 'WebPage', 'MailFolder',
'OrderedFolder', 'CommentRoot', 'BlogPost') AND ((_nxquery @@
"fulltext"."fulltext")) AND ((_nxquery2 @@ "fulltext"."fulltext")) UNION ALL
SELECT "_H"."id" AS "_C1" FROM "hierarchy" "_H" JOIN "proxies" ON "_H"."id" =
"proxies"."id" JOIN "hierarchy" ON "hierarchy"."id" = "proxies"."targetid" JOIN
"fulltext" ON "fulltext"."id" = "hierarchy"."id" JOIN "fulltext" ON
"fulltext"."id"
= "hierarchy"."id", TO_TSQUERY('spanish', 'Auto') _nxquery,
TO_TSQUERY('spanish', 'Haus') _nxquery2 WHERE "hierarchy"."primarytype" IN
('TaskLists', 'Dig
italization', 'SectionRoot', 'Univers', 'MailMessage', 'Workspace', 'Forum',
'ContextualLink', 'PictureBook', 'BlogSite', 'Post', 'Tag', 'Thread',
'search_results', 'Space', 'Picture', 'UserWorkspacesRoot', 'TemplateRoot',
'Document', 'Folder', 'AdvancedSearchCustom', 'Domain', 'File',
'WorkspaceRoot', 'Comment', 'AdvancedSearch', 'WebSite', 'Note', 'Gadget',
'HiddenFolder', 'QueryNav', 'Section', 'TaskList', 'WebPage', 'MailFolder',
'OrderedFolder', 'Com
mentRoot', 'BlogPost') AND ((_nxquery @@ "fulltext"."fulltext")) AND
((_nxquery2 @@ "fulltext"."fulltext"))) AS "_T" -- LIMIT 10 OFFSET 0 -- COUNT
TOTAL
-
RunSolutions
Open Source It Consulting
-
Email: [email protected]
Parc Bit - Centro Empresarial Son Espanyol
Edificio Estel - Local 3D
07121 - Palma de Mallorca
Baleares
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Mon, 23 Aug 2010 18:50:58 +0200
> From: Florent Guillaume <[email protected]>
> Subject: Re: [Ecm] Advanced search: ERROR: table name "fulltext"
> specified more than once
> To: Raimund Sacherer <[email protected]>
> Cc: [email protected]
> Message-ID:
> <[email protected]>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Please activate SQL-level debugging:
> In $JBOSS/server/default/conf/jboss-log4j.xml add:
> <category name="org.nuxeo.ecm.core.storage.sql">
> <priority value="TRACE" />
> </category>
> and bump the levelMin value to TRACE in the CONSOLE appender.
>
> Then have a look at the generated query at the PostgreSQL level that
> gives the error.
> It would be good if you also found somewhere in the logs the
> corresponding NXQL query.
>
> Florent
>
>
_______________________________________________
ECM mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
