Databasemodel Drupal 7: verschil tussen versies
Regel 559: | Regel 559: | ||
sku; | sku; | ||
</pre> | </pre> | ||
+ | |||
+ | == 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. | ||
== Zie ook == | == Zie ook == |
Versie van 30 mrt 2020 11:02
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.
Zie ook
Bronnen
- https://www.drupal.org/node/1785994 - Database schema
- https://www.drupal.org/developing/api/database - Database API
- https://api.drupal.org/api/drupal/includes!database!database.inc/group/database/7 - Database abstraction layer
- http://stackoverflow.com/questions/4950089/in-drupal-how-to-get-tnid-or-the-node-id-of-the-translated-node
Orderbedrag Drupal Commerce uitlezen