Databasemodel Drupal 7: verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
Regel 564: Regel 564:
 
Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.
 
Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.
  
−
* Waarschijnlijk gaat dit via ''line_items''
+
Tjakka: In een half uurtje gepiept:
−
* Ook orderstatus nodig, want alleen geslaagde orders meenemen.
+
 
 +
<pre>
 +
use kbo2;
 +
 
 +
drop table if exists kbo2_raw;
 +
 
 +
create table kbo2_raw
 +
 
 +
select
 +
    commerce_order.order_id,
 +
    commerce_order.status,
 +
    commerce_line_item.line_item_id,
 +
    commerce_line_item.type,
 +
    commerce_line_item.line_item_label,
 +
    commerce_line_item.quantity,
 +
    field_data_commerce_total.commerce_total_amount  # In centen, incl. BTW
 +
from
 +
    commerce_order
 +
 
 +
 
 +
####################################################################################
 +
# joins
 +
####################################################################################
 +
#
 +
join
 +
commerce_line_item
 +
    on
 +
commerce_order.order_id = commerce_line_item.order_id
 +
 
 +
join
 +
field_data_commerce_total
 +
    on
 +
commerce_line_item.line_item_id = field_data_commerce_total.entity_id
 +
 
 +
 
 +
####################################################################################
 +
# where
 +
####################################################################################
 +
#
 +
where
 +
commerce_order.status="Bedankt"
 +
or
 +
commerce_order.status="completed"
 +
or
 +
commerce_order.status="invoiced";
 +
</pre>
  
 
== Zie ook ==
 
== Zie ook ==

Versie van 30 mrt 2020 13:03

Drupal 7 Datamodel

Alle Drupal Commerce-tabellen

  • commerce_calculated_price
  • commerce_checkout_pane
  • commerce_customer_profile
  • commerce_customer_profile_revision
  • commerce_flat_rate_service
  • commerce_line_item
  • commerce_order
  • commerce_order_revision
  • commerce_payment_transaction
  • commerce_payment_transaction_revision
  • commerce_product
  • commerce_product_revision
  • commerce_product_type
  • commerce_tax_rate
  • commerce_tax_type
  • field_data_commerce_... - Maatwerk-velden
  • field_data_field_... - Maatwerk-velden
  • field_revision_commerce_... - Maatwerk-velden
  • field_revision_field_... - Maatwerk-velden.

Tabel - Node

Velden: 

  Naam        Omschrijving             Type           Default   Opmerkingen
  ----        ------------             -----------    -------   -----------
* nid         Node-ID                  Int(10)                  Primaire sleutel
* vid         Version-ID               Int(10)        NULL      Foreign key van tabel node_revisions
* type        Content-type             Varchar(32)    ''        Wat je vindt onder Structure > Content-types
* language                             Varchar(12)    ''
* title                                Varchar(255)   ''
* uid         User-ID                  Int(11)        0
* status      Published?               Int(11)        1
* created     Unix timestamp           Int(11)        0
* changed     Unix timestamp           Int(11)        0         'created' en 'changed' hebben vaak dezelfde waarde
* comment     Commentaar toegestaan?   Int(11)        0
* promote                              Int(11)        0
* sticky                               Int(11)        0
* tnid        Translation Node ID      Int(10)        0
* translate                            Int(11)        0

Tabel - node_revision

  Naam        Omschrijving             Datatype       Default
  ----        ------------             -----------    -------
* nid         Node-ID (FK)             INT(10)        '0'
* vid         Revision-ID (PK)         INT(10
* uid         User-ID                  INT(11)        '0'
* title       Node-title               VARCHAR(255)   ''
* log         ?                        LONGTEXT
* timestamp   Created                  INT(11)        '0'
* status      Published?               INT(11)        '1'
* comment     Commentaar toegestaan?   INT(11)        '0'
* promote     Promoted                 INT(11)        '0'
* sticky      Sticky on top?           INT(11)        '0'
* ds_switch   ?                        VARCHAR(255)   ''

Taxonomieën

Tabellen 

* taxonomy_index            (nid, tid, sticky, created)     Associatie tussen node & taxon
* taxonomy_term_data        (tid, vid, name, description)   Taxon
* taxonomy_term_hierarchy   (tid, parent)                   Hierarchie: ouder van een taxon
* taxonomy_vocabulary       (vid, name, machine_name, 
                             description, hierarchy,        Definitie van een vocabulaire
                             module, weight)

Veldnamen

Veldnamen zijn doorgaans consistent door de gehele database. Oftewel, veld 'nid' in de ene tabel, is dezelfde 'nid' in een andere tabel: 

* nid - Node ID
* tid - Term ID = Taxon ID
* vid - Vocabulary ID

Eigen velden - Voorbeeld

In Drupal 7 kun je aan een taxonomie eigen velden toevoegen. Elk veld vorm een eigen databasetabel. Dit is een voorbeeld waarbij een afbeelding is toegevoegd aan een taxonomie: 

Veldnaam: field_revision_field_taxonomie_afbeelding

Velden:

* entity_type
* bundle
* deleted
* entity_id
* revision_id
* language
* delta
* field_taxonomie_afbeelding_fid
* field_taxonomie_afbeelding_alt
* field_taxonomie_afbeelding_title
* field_taxonomie_afbeelding_width
* field_taxonomie_afbeelding_height

Omzet Drupal Commerce uitlezen

Net als bij Übercart voor Drupal 6, zou ik graag via SQL de omzet willen uitlezen uit Drupal Commerce in Drupal 7.

Tabellen

commerce_order

  • order_id - PK
  • order_number - Gebruikers-order-id
  • revision_id - FK voor tabel commerce_order_revision
  • created - Unix timestamp
  • status - Bv. completed.
  • data - Serialised dataveld (longblob) waarvan de inhoud in MySQL Workbench alleen te lezen is als je Open value in editor kiest , maar in de mysql-client prima prima is uit te lezen → Geen ordertotalen.

Voorbeeld data-veld: 

a:4:
{
	s:17:"last_cart_refresh";i:1420733641;
	s:14:"payment_method";
	s:38:"paypal_wps|commerce_payment_paypal_wps";
	s:20:"payment_redirect_key";
	s:43:"TGUOciUPKaZg3AU1237eLdwHNJdDqBLG0w5lCx9VEk";
	s:43:"commerce_payment_order_paid_in_full_invoked";
	b:1;
}

commerce_order_revision

Drupal slaat vaak veel info op in revision-velden. Elk record bevat daarbij de complete status van de onderhavige entiteit. Daarom is in dit geval alleen het laatste revision-veld relevant. Velden:

  • order_id - FK tabel commerce_order
  • order_number
  • revision_id
  • data (serialised).

Voorbeeld data-veld van de het laatste revisie-veld van een order met status completed: 

a:4:
{
   s:17:"last_cart_refresh";i:1420733641;
   s:14:"payment_method";
   s:38:"paypal_wps|commerce_payment_paypal_wps";
   s:20:"payment_redirect_key";
   s:43:"TGUOciUPKaZg3AU692c7eLdwHN123qBLG0w5lCx9VEk";
   s:43:"commerce_payment_order_paid_in_full_invoked";
   b:1;
}

field_data_commerce_order_total

  • commerce_order_total_amount - Inclusief BTW, uitgedrukt in centen
  • entity_id (FK) = order_id. Omdit dit een dynamisch gegenereerd veld is, is de benaming generiek. De waardes komen echter prima overeen met de order-PK.

Overige tabellen

  • commerce_order_revision - Ziet er niet relevant uit
  • commerce_line_item - Ziet er niet relevant uit.

API-calls

De koninklijke weg om deze data uit een webshop te peuteren, is via de vastgestelde API-calls te doen. Mocht ik dat ooit inderdaad doen: Heel moeilijk lijkt het niet te zijn. Nog een keer.

Commentaren verwijderen

Gevalletje spam-posting: Hoe verwijder ik 500 commentaren?

Structuur 

  Tabel                         Sleutel
  ---------------               ------------------------------
* comment                       PK: cid - Dit is de hoofdtabel
* users                         FK: uid
* node                          FK: nid
* role_permission               FK: role_permission_rid - ?
* field_data_comment_body       ?
* field_revision_comment_body   ?

Alle commentaar verwijderen

Maak voor de zekerheid eerst een backup - Ik ben slechts voor 95% zeker dat dit werkt. 

delete from field_revision_comment_body;
delete from field_data_comment_body;
delete from comment;

Drupal Commerce: Voorraadpositie op xyz-code wijzigen

Casus

De klant heeft een spreadsheet gemailed met producten + voorraadpositie. Echter: Niet de gebruikelijke SKU's worden gehanteerd, maar 'interne' xyz-codes (de codes van de logistieke partner om precies te zijn).

Datamodel

  • Tabel commerce_product: Dit betreft L1. Daar vind je product_id, sku en title. Verder weinig spannends

Drupal Commerce: Orders exporteren (feb. 2017)

Verrassend hoe ingewikkeld het bleek om 'gewoon' orders te exporteren. Volgende keer de API gebruiken (veel efficiënter + robuuster): 

-- =====================================================
-- Select
-- =====================================================
--
select
 
    -- commerce_order
    -- ==============================================================
    -- 
    commerce_order.order_id,
    commerce_order.mail                     as "order_mail",
    commerce_order.status                   as "order_status",
    from_unixtime(commerce_order.created)   as "order_created",
    from_unixtime(commerce_order.changed)   as "order_changed",
   
    -- commerce_order.order_number,
    -- commerce_order.type,
    -- commerce_order.uid,
    -- commerce_order.data,
    -- commerce_order.hostname,
 
    -- order_total
    -- =====================================================
    --
    field_data_commerce_order_total.commerce_order_total_amount        as "order_total_amount",
    field_data_commerce_order_total.commerce_order_total_currency_code as "order_currency_code",
 
    -- Users
    -- =====================================================
    --
    users.uid  as "user_id",
    users.name as "user_name",
 
    -- Shipping - salution
    -- =====================================================
    --
    field_data_field_salutation.field_salutation_value 
    as "shipping_salutation",
     
    -- shipping - first_name
    -- =====================================================
    --
    field_data_field_firstname.field_firstname_value 
    as "shipping_first_name",
 
    -- shipping - middle_name
    -- =====================================================
    --
    field_data_field_middlename.field_middlename_value 
    as "shipping_middle_name",
     
    -- shipping - last_name
    -- =====================================================
    --
    field_data_field_lastname.field_lastname_value 
    as "shipping_last_name",
 
    -- shipping_address_stree
    -- =====================================================
    --
    field_data_field_street.field_street_value 
    as "shipping_address_street",
 
    -- Housenumber
    -- ==============
    --
    field_data_field_housenumber.field_housenumber_value 
    as "shipping_address_housenumber",
 
    -- housenumberaddition
    -- ===================
    --
    field_data_field_housenumberaddition.field_housenumberaddition_value 
    as "shipping_address_housenumberaddition",
 
    -- city
    -- ====
    --
    field_data_field_cust_city.field_cust_city_value 
    as "shipping_address_city",
     
    -- zipcode
    -- =======
    --
    field_data_field_zipcode.field_zipcode_value 
    as "shipping_zipcode",
     
    -- Shipping country
    -- ===========================
    --
    field_data_field_country.field_country_iso2 
    as "shipping_address_country",
 
    -- Billing country
    -- =====================
    --
    -- field_data_field_country.field_country_iso2 
    -- as "shipping_address_country",
     
 
    -- order_line_item
    -- =====================================================
    -- 
    commerce_line_item.line_item_id     as "orderline_id",
    commerce_line_item.type             as "orderline_type",
    commerce_line_item.line_item_label  as "orderline_sku",
    commerce_line_item.quantity         as "orderline_quantity",
    
    -- commerce_line_item.created
    -- commerce_line_item.changed,
    -- commerce_line_item.data
 
    -- commerce_product - title
    -- ==========================================================
    --
    commerce_product.title as "orderline_title",
   
    -- orderline - unit price
    -- =========================================================
    --
    field_data_commerce_unit_price.commerce_unit_price_amount
    as "orderline_unit_price",

    field_data_commerce_unit_price.commerce_unit_price_currency_code
    as "orderline_unit_price_currency",
     
    -- orderline - total price
    -- ==========================================================
    --
    field_data_commerce_total.`commerce_total_amount`
    as "orderline_total_amount",

    `field_data_commerce_total`.`commerce_total_currency_code`
    as "orderline_currency"
     
-- =====================================================
-- Join
-- =====================================================
--
-- commerce_order - De basis
-- =========================
-- 
from commerce_order

-- commerce_order_revision - Niet nodig
-- ====================================
-- 
-- left join commerce_order_revision
-- on        commerce_order.revision_id = 
--           commerce_order_revision.revision_id

-- commerce_line_item
-- ==================
-- 
left join commerce_line_item
on        commerce_order.order_id = 
          commerce_line_item.order_id

-- users
-- =====
--
left join users
on   commerce_order.uid = 
     users.uid

-- field_revision_commerce_customer_address
-- ========================================
-- 
left join field_revision_commerce_customer_address
on        commerce_order.order_id = 
          field_revision_commerce_customer_address.entity_id
 
-- Shipping address
-- ======================================================
-- 
left join field_data_commerce_customer_shipping       
on        field_data_commerce_customer_shipping.entity_id = 
          commerce_order.order_id
 
-- Billing address
-- ======================================================
-- * Billing addresses worden in dezelfde tabel bijgehouden als shipping addresses.
--   Waarschijnlijk een alias defineren op die tabel om billing addresses te kunnen
--   extraheren
-- * In dit geval leek er geen gebruik te worden gemaakt van billing addresses
--
-- left join field_data_commerce_customer_billing
-- on        field_data_commerce_customer_shipping.entity_id = 
--           commerce_order.order_id

-- order_total
-- ======================================================
-- 
left join field_data_commerce_order_total
on        field_data_commerce_order_total.entity_id = 
          commerce_order.order_id
 
-- Shipping salutation
-- ===================
--
left join field_data_field_salutation
on        field_data_field_salutation.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping firstname
-- ==================
--
left join field_data_field_firstname 
on        field_data_field_firstname.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping middlename
-- ===================
--
left join field_data_field_middlename
on        field_data_field_middlename.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping lastname
-- =================
--
left join field_data_field_lastname
on        field_data_field_lastname.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping Street
-- ===============
--
left join field_data_field_street 
on        field_data_field_street.entity_id  =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping Housenumber
-- ====================
--
left join field_data_field_housenumber 
on        field_data_field_housenumber.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping housenumberaddition
-- ============================
-- 
left join field_data_field_housenumberaddition 
on        field_data_field_housenumberaddition.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping cust_city
-- ==================
--
left join field_data_field_cust_city
on        field_data_field_cust_city.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping zipcode
-- ================
--
left join field_data_field_zipcode
on        field_data_field_zipcode.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id

-- Shipping country
-- ================
--
left join field_data_field_country
on        field_data_field_country.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Billing country
-- ===============
--
-- left join    field_data_field_country
-- on           field_data_field_country.entity_id = 
--              field_data_commerce_customer_billing.commerce_customer_billing_profile_id
 
-- commerce_product
-- ========================
--
left join commerce_product
on        commerce_product.sku = 
          commerce_line_item.line_item_label
 
-- Order_line - Unit price
-- =============================
--
left join field_data_commerce_unit_price
on        field_data_commerce_unit_price.entity_id = 
          commerce_line_item.line_item_id
 
-- Order_line - Total
-- ===================================
--
left join field_data_commerce_total
on        field_data_commerce_total.entity_id = 
          commerce_line_item.line_item_id
 
-- =====================================================
-- Sort order
-- =====================================================
--
order by commerce_order.order_id asc;

Drupal Commerce: Populairste producten (aug. 2019)

Dat gaat dan toevallig weer heel gemakkelijk: 

select
	line_item_label as sku,
	sum(quantity)   as quant 

from commerce_line_item
group by line_item_label
order by quant desc;

Drupal Commerce: Prijzen uitlezen (dec. 2019)

Wéér een stap naar een Drupal-loze wereld: 

select
   sku,
   title,
   commerce_price_amount				as sell_price_eurocent_incl_vat,
   commerce_price_amount/100				as sell_price_euro_incl_vat,
   (commerce_price_amount/100)/1.21			as sell_price_euro_ex_vat,
   # cast(commerce_price_amount as decimal(4,2))	as price_04	# Werkt niet!
   round((commerce_price_amount/100)/1.21,2) 		as sell_price_euro_ex_vat_rounded_2_decimals
   # commerce_price_data
from
   commerce_product
join
   field_data_commerce_price
on
   commerce_product.product_id = field_data_commerce_price.entity_id
order by
   sku;

Drupal Commerce: Omzet per artikel uitlezen (maart 2020)

Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.

Tjakka: In een half uurtje gepiept: 

use kbo2;

drop table if exists kbo2_raw;

create table kbo2_raw

select
    commerce_order.order_id,
    commerce_order.status,
    commerce_line_item.line_item_id,
    commerce_line_item.type,
    commerce_line_item.line_item_label,
    commerce_line_item.quantity,
    field_data_commerce_total.commerce_total_amount   # In centen, incl. BTW
from 
    commerce_order


####################################################################################
# joins
####################################################################################
#
join
	commerce_line_item 
    on 
	commerce_order.order_id = commerce_line_item.order_id

join
	field_data_commerce_total
    on
	commerce_line_item.line_item_id = field_data_commerce_total.entity_id


####################################################################################
# where
####################################################################################
#
where
	commerce_order.status="Bedankt"
or
	commerce_order.status="completed"
or
	commerce_order.status="invoiced";

Zie ook

Bronnen

Orderbedrag Drupal Commerce uitlezen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Databasemodel_Drupal_7&oldid=21254"