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!