0

I've got a problem with an view on my database. All other views are working correctly, but when I'm trying to add a GROUP BY statement behind my query, then I'll get the next Error:

1214 - The used table type doesn't support FULLTEXT indexes

I've set all my tables to ISAM, and i noticed the whole problem is in the GROUP BY statement.

My query looks like this:

CREATE VIEW `id_winkels`
AS
SELECT 
    `w`.`w_id` AS `w_id`,
    `w`.`k_id` AS `k_id`,
    `w`.`w_naam` AS `w_naam`,
    `w`.`w_logo` AS `w_logo`,
    `w`.`w_homepage` AS `w_homepage`,
    `w`.`w_straat` AS `w_straat`,
    `w`.`w_huisnummer` AS `w_huisnummer`,
    `w`.`w_postcode` AS `w_postcode`,
    `w`.`w_woonplaats` AS `w_woonplaats`,
    `w`.`w_land` AS `w_land`,
    `w`.`w_actief` AS `w_actief`,
    `w`.`w_datum` AS `w_datum`,
    COUNT(`p`.`p_id`) AS `totaal`
FROM `Winkels` `w` 
LEFT JOIN `Producten` `p`
        ON `w`.`w_id` = `p`.`w_id`
GROUP BY `w`.`w_naam`

Is there another option to count the amount of products by my shop without useing a group by?

When I'm running the query without the group by statement, it will return my total amount of product in one row.

Community
  • 1
  • 1
K. Takens
  • 33
  • 8
  • Why do you even group by `w_datum`? When you group by `w_datum` then you get one result line per `w_datum` of course. Is this what you actually want? Your problem description doesn't indicate this. – Thorsten Kettner Nov 05 '15 at 15:48
  • i'm sorry, i added it later.. it was GROUP BY w_naam, so ill get the count of all product by shop – K. Takens Nov 05 '15 at 15:57

1 Answers1

0

As you only select fields from Winkels, you should get the product count in a subquery rather than joining the tables:

CREATE VIEW id_winkels AS 
  SELECT 
    w.w_id AS w_id,
    w.k_id AS k_id,
    w.w_naam AS w_naam,
    w.w_logo AS w_logo,
    w.w_homepage AS w_homepage,
    w.w_straat AS w_straat,
    w.w_huisnummer AS w_huisnummer,
    w.w_postcode AS w_postcode,
    w.w_woonplaats AS w_woonplaats,
    w.w_land AS w_land,
    w.w_actief AS w_actief,
    w.w_datum AS w_datum,
    (
      select count(*)
      from Producten p 
      where p.w_id = w.w_id
    ) AS totaal 
  FROM Winkels w;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • I've tried to search in my table now, and i still get the same error. The query is working well while i'm not trying to filter. But when i try to search by field, i get the same error. – K. Takens Nov 05 '15 at 16:04
  • Er, what? Are you saying that it is not possible to add a WHERE clause? Or what else are you trying? The error seems to indicate that you are trying to use FULL TEXT features (`MATCH ... AGAINST`), but your table type is not up to this. Check here: http://stackoverflow.com/questions/963534/mysql-fulltext-indexes-issue. And do you actually *want* to use FULL TEXT searches? – Thorsten Kettner Nov 06 '15 at 08:54
  • Yeah i really want a ful text search, i found out, that my query works, while i dont add the count part in my view, so i am trying to add it to my query now. The only problem left now is: it counts the total of all product instead of total product by shop – K. Takens Nov 06 '15 at 09:00
  • This is my query so far btw: SELECT SQL_CALC_FOUND_ROWS w_id, k_id, w_logo, w_naam, w_actief, w_woonplaats, w_datum ,(SELECT COUNT(w_id) FROM Producten p WHERE p.w_id = w_id)AS totaal FROM id_winkels_checkdefout WHERE (match(w_naam, w_woonplaats) against( :zoeken in boolean mode)) AND $where $order LIMIT $limit",[":zoeken" => $zoekterm]); – K. Takens Nov 06 '15 at 09:06
  • Aren't `Winkels` the stores and `w_id` their IDs? The subquery counts products for a `w_id`, so I don't see how it can possibly count all products instead of the shops' products. You must be mistaken. (BTW: Why do you use `count(w_id)` instead of `count(*)`? It does exactly the same, but just looks confusing, because you don't count stores here, as `count(w_id)` suggests.) – Thorsten Kettner Nov 06 '15 at 09:43
  • My fault, it was a typo, even with count(*) i didn't get the right data. Now i made a new function wich counts all products by $w_id and returning the value of this into my datatables. This won't be the fast way to get my result, but i get the results i was wishing for. Thanks allot for all the help! – K. Takens Nov 06 '15 at 10:08
  • I just spotted your error in `SELECT COUNT(w_id) FROM Producten p WHERE p.w_id = w_id`. The last `w_id` has no qualifier, so it belongs to the current table. So you evaluate `p.w_id = p.w_id` which is always true. Make it `p.w_id = w.w_id` instead. – Thorsten Kettner Nov 06 '15 at 10:19
  • I've tried to add w.w_id allready before, but it didn't help. I also tried to add another select in the query, but even that won't work :P haha! – K. Takens Nov 06 '15 at 10:28