0

I manage a Wordpress site for a football team, and I created therefore a SQL Query to calculate the standings. Now I want to switch over to Drupal and for the calculation of the standings I thought to use the views module. Is it possible to integrate my SQL Query into Views?

Query :

$sql="SELECT 
  teamname AS Team, Sum(Played) AS Played, Sum(Won) AS Won, Sum(Draw) AS Draw, Sum(Lost) AS Lost,
  SUM(Gfor) as Gfor, SUM(Gagainst) AS Gagainst, SUM(Gdiff) AS Gdiff, SUM(Pts) AS Pts


FROM(
  SELECT
    teamhome Team,
    1 Played,
    IF(homescore > awayscore,1,0) Won,
    IF(homescore = awayscore,1,0) Draw,
    IF(homescore < awayscore,1,0) Lost,
    homescore Gfor,
    awayscore Gagainst,
    homescore-awayscore Gdiff,
    CASE WHEN homescore > awayscore THEN 3 WHEN homescore = awayscore THEN 1 ELSE 0 END Pts
  FROM matches
WHERE homescore IS NOT NULL AND awayscore IS NOT NULL
AND division='1' and season = '2015-2016' AND matchtype='C'
  UNION ALL
  SELECT
    teamaway,
    1,
    IF(homescore < awayscore,1,0),
    IF(homescore = awayscore,1,0),
    IF(homescore > awayscore,1,0),
    awayscore,
    homescore,
    awayscore-homescore Gdiff,
    CASE WHEN homescore < awayscore THEN 3 WHEN homescore = awayscore THEN 1 ELSE 0 END
  FROM matches
  WHERE homescore IS NOT NULL AND awayscore IS NOT NULL
  AND division='1' and season = '2015-2016' AND matchtype='C'
) as tot

JOIN teams t ON tot.Team=t.teamid
GROUP BY Team

ORDER BY SUM(Pts) DESC, Sum(Won) DESC, SUM(Gdiff) DESC";
  • What do you mean by "switch over to Drupal"? Do you want to "migrate" your WP data to a Drupal site entirely? Or do you rather want to have "a Drupal site" in which you want to show those SQL results by still launching that same SQL query to your WP site, which continues to exist? – Pierre.Vriens Apr 6 '16 at 19:22
2

You will need to create a (1) custom table in the drupal database, (2) to adopt that table using the data module and (3) to create a view with that table as base the table of your view.

1) Create your custom table by using pure SQL (the fields order matters)

DROP TABLE IF EXISTS my_custom_table;
CREATE TABLE `my_custom_table` (
`node_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The primary identifier for a node.',
`int_field` int(10) unsigned DEFAULT NULL,
`text_field_1` varchar(255) NOT NULL DEFAULT,
`text_field_2` varchar(255) DEFAULT NULL,
`long_text_field` longtext,
`term_tid_field` int(10) unsigned NOT NULL DEFAULT '0',
`term_field` varchar(255) NOT NULL DEFAULT,
 PRIMARY KEY (`node_id`),
 KEY `text_field_1` (`text_field_1`),
 KEY `text_field_2` (`text_field_2`),
 KEY `long_text_field` (`long_text_field`),
 KEY `term_tid_field` (`term_tid_field`),
 KEY `term_field` (`term_field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You can peek at the drupal database for more field types

1.1) Continue by adding your data into that table by using SQL again:

INSERT IGNORE INTO my_custom_table(
SELECT
 int_field,
 text_field_1,
 text_field_2,
 long_text_field,
 term_tid_field,
 term_field
FROM
// Your query
);

2) Go to the data module's admin page admin/structure/data and adopt your custom table. The module is smart enough and your table will be displayed in the list, simple adopt it.

3) Create a new view admin/structure/views/add and if everything went well your table will be in the Show list with all other available tables. You should be able to see all the fields (1:1) from your table as fields in the view.

A drawback (if it really is) by following that method is that no other fields are available, like node fields, taxonomy, users etc, just yours. The same goes for contextual filters handlers, filters criteria handlers and relationships. That's all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.