Welcome, Guest
Username: Password: Remember me
If you expect answers you should describe your problem and give as much information's as possible. (SQL-Structure, Template Code, Joomla Version ...) Please read this before posting: joodb.feenders.de/support.html
  • Page:
  • 1
  • 2

TOPIC: Use of views

Use of views 2 months 3 weeks ago #5512

  • Karel.vancompenolle
  • Karel.vancompenolle's Avatar Topic Author
  • Offline
  • Posts: 4
  • Thank you received: 0
I order to optimize the tables in my SQL database, I needed a view for optimal query's.
When I try to use the query in JooDatabase Pro, i have a problem because there is no index on the view.

Is there a sommution for this problem?

Please Log in or Create an account to join the conversation.

Use of views 2 months 3 weeks ago #5513

  • Dirk
  • Dirk's Avatar
  • Offline
  • Posts: 1109
  • Karma: 21
  • Thank you received: 202
I am not shure I understand your problem.

U1sually every table sholud have a key field. Usually it's a integer field set as PRIMARY UNIQUE AUTOINDEX.

If you don't have such field you should open PhpMaydmin or whatever and enter.
ALTER TABLE YOUR_TABLE_NAME  ADD `id` INT(6) NOT NULL AUTO_INCREMENT  FIRST,  ADD   PRIMARY KEY  (`id`);

Replace your YOUR_TABLE_NAME

You can also add indexes to your title and date fields and all fields type VARCHAR.

Make sure that your main Index field is UNIQUE, indexed and not a text-type field.
Best varchar (128) , DATETIME or integer.

Please Log in or Create an account to join the conversation.

Use of views 2 months 3 weeks ago #5514

  • Karel.vancompenolle
  • Karel.vancompenolle's Avatar Topic Author
  • Offline
  • Posts: 4
  • Thank you received: 0
You did'nt understand my real problem.
I created the tables with primary keys, but the view I created is a combination of 3 tables.
The relation between the tables is a bit complex, because there is a join from table 1 to table 2 and another join from table 2 to table 3.
I tried to make this relation in JooDatabse, but without succes.
Therefore I created the view in the database
I do not see how to create an index on the view.
From Joodabase (in Joomla) I see the view as a table, but I can not use it as it has no index.

Please Log in or Create an account to join the conversation.

Use of views 2 months 3 weeks ago #5515

  • Dirk
  • Dirk's Avatar
  • Offline
  • Posts: 1109
  • Karma: 21
  • Thank you received: 202
With the PRO version it should work!

Typical n:m relation

TABLE1.id ---> id1.INDEX_TABLE.id2 <--- TABLE2.id

Please Log in or Create an account to join the conversation.

Use of views 2 months 3 weeks ago #5517

  • Karel.vancompenolle
  • Karel.vancompenolle's Avatar Topic Author
  • Offline
  • Posts: 4
  • Thank you received: 0
How do I understand your answer ?
Do I have to create an extra table in the database, combining the index of the original tables
or do I use the linked table function of Joodatabase ?

Please Log in or Create an account to join the conversation.

Use of views 2 months 1 week ago #5531

  • Dirk
  • Dirk's Avatar
  • Offline
  • Posts: 1109
  • Karma: 21
  • Thank you received: 202
Joodb can not join tables in the 3rd level.

If you have something like
MOVIES => ACTORS its no problem

But if you want fo do something like
MOVIES => ACTORS => Countries

You have 4 options
1: Use a mysql wiew joining the results
2: Use ACTORS as your main table!
3: Create a second Joodb database and include the information of your subtable as plugin. (Joodb PRO)
4: Create a custom JooDB - Plugin

But its complicate.

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
  • 2
Moderators: Dirkjoest