File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes construct complex table view Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "construct complex table view" Watch "construct complex table view" New topic

construct complex table view

Yahya Elyasse
Ranch Hand

Joined: Jul 07, 2005
Posts: 510

hi guys,
we want to generate a mysql view table to use it for Full text search
here's what we want to do :
select title & description from a table (mods)
also use a field as a concatenated list of "tags" (table mod_tags)
so if tag rows for mod_id = 62 are:
we should find those three rows in db (select tag from mod_tags where mod_id = 62).... and concatenate them into: "bird firetruck hamburger"
after that add this concatenated string as a field to the view table.
then also use F(rating, num_views, date) to determine sorting
where F is a function we define
e.g., F(rating, views, date) = A*(views * rating^2) + B*(today - date)
where A, B are constants
views ;ratings ,date are selected from another table (mods_meta)
this function F should be added as field in view
so 4 fields for view: title, description, CONCAT(tags), F(rating, num_views, date)

i would appreciate if someone suggest me how to construct this sql view ?
we prefer as much performance & efficiency building this view.

thanks for your interest.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

we prefer as much performance & efficiency building this view.

Relational databases have never been very good a free text searching. Have you considered using something like Lucene to provide the search indices? In my experience, Lucence has far richer search support and performs considerably better than a database backed complex search solution.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Yahya Elyasse
Ranch Hand

Joined: Jul 07, 2005
Posts: 510

thanks for your reply,

we are considering to use

we need to first build the view than feed it to sphinx config file .

i would appreciate if you help constructing the view as i described above.

I agree. Here's the link:
subject: construct complex table view
It's not a secret anymore!