27
Advanced Wordpress Queries Jean Carlo Nascimento aka SUISSA Líder Técnico Email: [email protected] Twitter: @osuissa http://about.me/suissa Nodeware http://www.nodeware.com.br Twitter:@nodeware

Advanced Wordpress Queries

Embed Size (px)

DESCRIPTION

Palestra de Jean Carlos Nascimento "Suissa" apresentada durante o 7Masters - Encontro iMasters de Especialistas em WordPress - setembro/2012.

Citation preview

Page 1: Advanced Wordpress Queries

Advanced Wordpress Queries

Jean Carlo Nascimento aka SUISSALíder TécnicoEmail: [email protected] Twitter: @osuissahttp://about.me/suissa

Nodewarehttp://www.nodeware.com.br Twitter:@nodeware

Page 2: Advanced Wordpress Queries

Sobre o autor

Desenvolvedor Web há 8 anosFormado pela UTF-PR em SIEspecialista em Desenvolvimento WebCriador do Sampa.js e FrontinSampaEvangelista Node.js e NoSQLnosqlbr.com.brjavascriptbrasil.comjquerybrasil.org

Page 3: Advanced Wordpress Queries

VantagensPor exemplo, a API do WordPress facilita a consulta para o número de comentários por post, mas o que acontece se você quiser puxar para trás o número de comentários para cada usuário por mensagem (ou por página) e ordená-los por data ou pelo número de vezes que eles comentaram? Obviamente, você precisa escrever uma consulta avançada.

Page 4: Advanced Wordpress Queries
Page 5: Advanced Wordpress Queries
Page 6: Advanced Wordpress Queries

Tabelaswp_comments: dados dos Comentárioswp_commentmeta: meta data de cada Comentáriowp_links: dados do Linkswp_options: todas as opções de Administration > Settingswp_posts: dados dos Posts e Páginaswp_postmeta: meta data de cada Postwp_terms: dados das Categorias e Tags de Posts e Páginas wp_term_relationships: dados dos relacionamentos entre Posts, categories, and tagswp_term_taxonomy: descrições das Tags, Links e/ou Categorias gravadas em wp_termswp_users: dados dos Usuárioswp_usermeta: meta data de cada usuário

Page 7: Advanced Wordpress Queries
Page 8: Advanced Wordpress Queries

CamposTable: wp_commentmetaField Type Null Key Default Extrameta_id bigint(20) unsigned PRI NULL auto_incrementcomment_id bigint(20) unsigned IND 0 FK->wp_comments.comment_idmeta_key varchar(255) YES IND NULL meta_value longtext YES NULL Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 0 meta_IDcomment_id INDEX none comment_idmeta_key INDEX none meta_keyTable: wp_commentsField Type Null Key Default Extracomment_ID bigint(20) unsigned PRI NULL auto_incrementcomment_post_ID bigint(20) unsigned IND 0 FK->wp_posts.IDcomment_author tinytext comment_author_email varchar(100) comment_author_url varchar(200) comment_author_IP varchar(100) comment_date datetime 0000-00-00 00:00:00 comment_date_gmt datetime IND & IND Pt2 0000-00-00 00:00:00 comment_content text comment_karma int(11) 0 comment_approved varchar(20) IND & Ind Pt1 1 comment_agent varchar(255) comment_type varchar(20) comment_parent bigint(20) unsigned 0 FK->wp_comments.IDuser_id bigint(20) unsigned 0 FK->wp_users.IDIndexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 1 comment_IDcomment_post_ID INDEX None comment_post_IDcomment_approved_date_gmtINDEX None comment_approvedcomment_date_gmtcomment_date_gmt INDEX None comment_date_gmtcomment_parent INDEX None comment_parentTable: wp_linksField Type Null Key Default Extralink_id bigint(20) unsigned PRI NULL auto_incrementlink_url varchar(255) link_name varchar(255) link_image varchar(255) link_target varchar(25) link_description varchar(255) link_visible varchar(20) IND Y link_owner bigint(20) unsigned 1 link_rating int(11) 0 link_updated datetime 0000-00-00 00:00:00 link_rel varchar(255) link_notes mediumtext link_rss varchar(255) Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 7 link_IDlink_category INDEX None link_categorylink_visible INDEX None link_visibleTable: wp_optionsField Type Null Key Default Extraoption_id bigint(20) unsigned PRI Pt1 NULL auto_incrementoption_name varchar(64) PRI Pt3 & IND option_value longtext autoload varchar(20) yes Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 184 option_idblog_idoption_nameoption_name UNIQUE 184 option_nameTable: wp_postmetaField Type Null Key Default Extrameta_id bigint(20) unsigned PRI NULL auto_incrementpost_id bigint(20) unsigned IND 0 FK->wp_posts.IDmeta_key varchar(255) YES IND NULL meta_value longtext YES NULL Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 13 meta_IDpost_id INDEX 15 post_idmeta_key INDEX 7 meta_keyTable: wp_postsField Type Null Key Default ExtraID bigint(20) unsigned PRI & IND Pt4 auto_incrementpost_author bigint(20) unsigned 0 FK->wp_users.IDpost_date datetime IND Pt3 0000-00-00 00:00:00 post_date_gmt datetime 0000-00-00 00:00:00 post_content longtext post_title text post_excerpt text post_status varchar(20) IND PT2 publish comment_status varchar(20) open ping_status varchar(20) open post_password varchar(20) post_name varchar(200) IND to_ping text pinged text post_modified datetime 0000-00-00 00:00:00 post_modified_gmt datetime 0000-00-00 00:00:00 post_content_filtered longtext post_parent bigint(20) unsigned 0 FK->wp_posts.IDguid varchar(255) menu_order int(11) 0 post_type varchar(20) IND Pt1 post post_mime_type varchar(100) comment_count bigint(20) 0 Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 2 IDpost_name INDEX None post_nametype_status_dateINDEX None post_typepost_statuspost_dateIDpost_parent INDEX None post_parentpost_author INDEX None post_authorTable: wp_termsField Type Null Key Default Extraterm_id bigint(20) unsigned PRI auto_incrementname varchar(200) slug varchar(200) UNI term_group bigint(10) 0 Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 2 term_IDslug UNIQUE 2 slugname Index none nameTable: wp_term_relationshipsField Type Null Key Default Extraobject_id bigint(20) unsigned PRI Pt1 0 term_taxonomy_id bigint(20) unsigned PRI Pt2 & IND 0 FK-

>wp_term_taxonomy.term_taxonomy_idterm_order int(11) 0 Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 8 object_idterm_taxonomy_idterm_taxonomy_id INDEX None term_taxonomy_idTable: wp_term_taxonomyField Type Null Key Default Extraterm_taxonomy_id bigint(20) unsigned PRI auto_incrementterm_id bigint(20) unsigned UNI Pt1 0 FK->wp_terms.term_idtaxonomy varchar(32) UNI Pt2 description longtext parent bigint(20) unsigned 0 count bigint(20) 0 Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 2 term_taxonomy_idterm_id_taxonomy UNIQUE 2 term_idtaxonomytaxonomy INDEX None taxonomyTable: wp_usermetaField Type Null Key Default Extraumeta_id bigint(20) unsigned PRI NULL auto_incrementuser_id bigint(20) unsigned '0' FK->wp_users.IDmeta_key varchar(255) Yes IND NULL meta_value longtext Yes IND NULL Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 9 umeta_iduser_id INDEX None user_idmeta_key INDEX None meta_keyTable: wp_usersField Type Null Key Default ExtraID bigint(20) unsigned PRI NULL auto_incrementuser_login varchar(60) IND user_pass varchar(64) user_nicename varchar(50) IND user_email varchar(100) user_url varchar(100) user_registered datetime 0000-00-00 00:00:00 user_activation_key varchar(60) user_status int(11) 0 display_name varchar(250) Indexes

Keyname Type Cardinality FieldPRIMARY PRIMARY 1 IDuser_login_key INDEX None user_loginuser_nicename INDEX None user_nicename

Page 9: Advanced Wordpress Queries
Page 10: Advanced Wordpress Queries
Page 11: Advanced Wordpress Queries

Classe Wpdb

Prove uma interface de acesso ao banco de dados vagamente baseado na classe ezSQL.

Page 12: Advanced Wordpress Queries
Page 13: Advanced Wordpress Queries

Classe Wpdb - API

get_rowget_colget_var

get_results

queryinsertupdateprepare

insert_id

show_errors

Page 14: Advanced Wordpress Queries

Classe Wpdb - get_row<?php $posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish' AND post_type='post' ORDER BY comment_count DESC LIMIT 0,1")

// Título do post mais comentado echo $posts->post_title;?>

Page 15: Advanced Wordpress Queries

Classe Wpdb - get_col<?php $posts = $wpdb->get_col("SELECT ID FROM wp_posts WHERE post_status = 'publish' AND post_type='post' ORDER BY comment_count DESC LIMIT 0,10")

// Quarto comentário mais comentado echo $posts[3]->ID;?>

Page 16: Advanced Wordpress Queries

Classe Wpdb - get_var<?php $email = $wpdb->get_var("SELECT user_email FROM wp_users WHERE user_login = manolo ")

// email do manolo echo $email;?>

Page 17: Advanced Wordpress Queries

Classe Wpdb - get_results<?php $posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'future' AND post_type='post' ORDER BY post_date ASC LIMIT 0,4")

// Título do primeiro post agendado echo $posts[0]->post_title;?>

Page 18: Advanced Wordpress Queries

Classe Wpdb - query<?php $wpdb->query("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' ");

?>

Page 19: Advanced Wordpress Queries

Classe Wpdb - insert<?php/*$wpdb->insert( $table, $data, $format);*/ $wpdb->insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "bazinga", "meta_value" => 10), array("%d", %s", "%d"));

// INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "bazinga", 10);?>

Page 20: Advanced Wordpress Queries

Classe Wpdb - update<?php/*$wpdb->update( $table, $data, $where, $format = null, $where_format = null );*/

$wpdb->update( $wpdb->posts, array("post_title" => "Manhê modifiquei"), array("ID" => 5), array("%s"), array("%d") );

?>

Page 21: Advanced Wordpress Queries

E a segurança disso tudo?

Page 22: Advanced Wordpress Queries
Page 23: Advanced Wordpress Queries

Classe Wpdb - prepare<?php $sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 );

$wpdb->query($sql);

/*$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );*/?>

Page 24: Advanced Wordpress Queries

Classe Wpdb - insert_id<?php $sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 );

$wpdb->query($sql); $meta_id = $wpdb->insert_id;

/*$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );*/?>

Page 25: Advanced Wordpress Queries
Page 26: Advanced Wordpress Queries

Classe Wpdb - show_errors$wpdb->show_errors(); $wpdb->query("DELETE FROM wp_posts WHERE post_id = 554 ");

// A mensagem de erro irá dizer-lhe que o campo "post_id" é um campo // desconhecido nesta tabela (já que o campo correto é ID)

Page 27: Advanced Wordpress Queries