Lou's lab

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== databank======
[[drupal:drupal|{{ :drupal:icon.jpg?120|}}]]

===== context=====
dit document beschrijft de databank in Drupal
===== tabellen=====
  - node: unieke node_id (nid)
  - node_field_data: metadata over node (aanmaak-, wijzigingsdatum, ...)
  - <nowiki>node_body</nowiki>: inhoud
  - node_revison: nid, vid
  - node_field_revision: metadata over node (aanmaakdatum, userid)
  - <nowiki>node_revision_body</nowiki>: inhoud revisie
  - users: overzicht gebruikers
  - users_field_data: detail over gebruikers (naam, email, userid, ...)
  - <nowiki>node_field_<naam></nowiki>: velden die je gebruikt binnen je node.

elke node heeft data in onderstaande tabellen
  * node: nid, type (content type)
  * node_body: eigenlijke inhoud van de node
  * node_field_data: metadata over node: oa titel
  * node_revision: info over revisies
  * node_revision_body: inhoud van elke revisie
  * node_field_revision: metadata over revisies

===== queries =====
==== tijdsbepaling ==== 
 ahv volgende functies:
  * FROM_UNIXTIME(<timestamp>):unixepoch in human-readable
  * NOW(): huidige datum\\ ''SELECT NOW()''
  * INTERVAL xx HOUR/DAY/MONTH/YEAR: xx uur/dagen/maanden/jaar seconden vroeger (-) of later (+)\\ ''SELECT NOW() - INTERVAL 1 HOUR'': 1 uur geleden
  * YEAR(<timestamp>): vis het jaar uit de timestamp\\ ''SELECT YEAR(FROM_UNIXTIME(created)) FROM l7gh_node_field_data ''
  * MONTH(<timestamp>): vis de maand uit de timestamp\\ ''SELECT MONTH(FROM_UNIXTIME(created)) FROM l7gh_node_field_data''
  * DAY(<timestamp>): vis de dag uit de timestamp\\ ''SELECT DAY(FROM_UNIXTIME(created)) FROM l7gh_node_field_data''


==== alle entries op de site ====

<code>SELECT f.title,FROM_UNIXTIME(f.created) AS "Time created",FROM_UNIXTIME(f.changed) AS "Time modified", f.type,tax.name AS "Plaats" FROM l7gh_node_field_data f   
LEFT JOIN l7gh_node__field_plaats pl ON f.nid = pl.entity_id   
LEFT JOIN l7gh_taxonomy_term_field_data tax ON pl.field_plaats_target_id = tax.tid  
ORDER BY FROM_UNIXTIME(f.changed) ASC;
</code>

==== alle entries die de laatste week gewijzigd zijn ====

<code>SELECT title,nid,FROM_UNIXTIME(created) AS "Time created",FROM_UNIXTIME(changed) AS "Time Last modified" FROM l7gh_node_field_data 
WHERE FROM_UNIXTIME(changed) > NOW() - INTERVAL 1 WEEK 
ORDER BY FROM_UNIXTIME(changed) DESC;
</code>

====overzicht (in aantal) per categorie====
<code>SELECT COUNT(title) AS Aantal,type FROM l7gh_node_field_data 
GROUP BY type 
ORDER BY Aantal DESC;
</code>

====overzicht (in aantal) per jaar====
<code>SELECT COUNT(title) AS "TOTAL ENTRIES",YEAR(FROM_UNIXTIME(created)) AS "YEAR" FROM l7gh_node_field_data 
GROUP BY YEAR(FROM_UNIXTIME(created)) 
ORDER BY YEAR(FROM_UNIXTIME(created)) DESC;
</code>

====overzicht (in aantal dagboekentries) per maand====
<code>SELECT COUNT(title) AS "TOTAL ENTRIES",MONTH(FROM_UNIXTIME(created)) AS "MONTH" FROM l7gh_node_field_data 
WHERE type = 'dagboek' 
GROUP BY MONTH(FROM_UNIXTIME(created)) 
ORDER BY COUNT(title) DESC;
</code>

====overzicht nodes met aantal revisies====
<code>SELECT nid AS "Node ID",title,COUNT(vid) AS "Aantal revisies",FROM_UNIXTIME(created),FROM_UNIXTIME(changed) FROM l7gh_node_field_revision 
GROUP BY nid;
</code>

====alle dagboekbladen die al werden afgeprint====
<code>SELECT title AS "Entries in schrift" FROM l7gh_node_field_data INNER JOIN l7gh_node__field_geprint_ ON l7gh_node_field_data.nid = l7gh_node__field_geprint_.entity_id WHERE field_geprint__value = 1 and type = 'dagboek';</code>
====alle dagboekbladen die nog niet werden afgeprint====

<code>SELECT title AS "Entries in schrift" FROM l7gh_node_field_data LEFT JOIN l7gh_node__field_geprint_ ON l7gh_node_field_data.nid = entity_id WHERE field_geprint__value = 0 AND type = 'dagboek';</code>

====idem maar de dit keer met de node ID====

<code>SELECT nid AS "Node ID" FROM l7gh_node_field_data LEFT JOIN l7gh_node__field_geprint_ ON l7gh_node_field_data.nid = entity_id WHERE field_geprint__value IS NULL AND type = 'dagboek';</code>

====voor een groep nodes een entry in een tabel aanmaken====
<code>INSERT INTO l7gh_node__field_geprint_ (entity_id,field_geprint__value) SELECT a.nid,p.field_geprint__value FROM l7gh_node_field_data a LEFT JOIN l7gh_node__field_geprint_ p ON a.nid = p.entity_id WHERE field_geprint__value IS NULL AND type = 'dagboek';</code>

====alle nodes van type verslag====
<code>SELECT f.title, FROM_UNIXTIME(f.created), b.body_value FROM `l7gh_node__body` b INNER JOIN l7gh_node_field_data f ON f.nid = b.entity_id WHERE b.bundle = 'verslag'
</code>


===== meer info =====
voeg hier linken toe naar verdere uitleg
{{tag>drupal}}