Make my MySQL query less verbose -


the following query real challenge me build wordpress site using wordpress wp_post table , wp_postmeta table , wp_user table. works contains lot of repeated statements. not sure how simplify (or if can be). tips simplifying , ridding repetition appreciated.

the tables contain data this:

wp_posts  id | post_author | post_parent | post_type    | post_title | post_date 2258  163           0             fep_message    2262  1             2258          fep_message    re:a 2264  163           2258          fep_message    re:a 1698  1             0             fep_message    b 1692  1             0             fep_message    c  wp_postmeta meta_id | post_id | meta_key          | meta_value 14696     2258      _fep_participants   1 14697     2258      _fep_participants   163 9819      1698      _fep_participants   163 9820      1698      _fep_participants   1 9759      1692      _fep_participants   163 9760      1692      _fep_participants   1 9815      1692      _fep_delete_by_1    1499496054 13751     1698      _fep_delete_by_163  1501044119  wp_user id   | user_login  1      myname 163    theirname

this query

select a.* ( select p.id, p.post_date, p.post_title, uf.user_login from_login, ut.user_login to_login  wp_posts p     join wp_postmeta pm_to     on  (p.id = pm_to.post_id      , pm_to.meta_key = '_fep_participants'      , p.post_parent = 0      , pm_to.meta_value <> p.post_author)     join wp_postmeta pm_delete     on (p.id = pm_delete.post_id) left join wp_users uf on uf.id = p.post_author  left join wp_users ut on ut.id = pm_to.meta_value     (p.post_type = 'fep_message' , pm_delete.meta_value <> '_fep_delete_by_1' , uf.user_login = 'myname' or p.post_type = 'fep_message' , pm_delete.meta_value <> '_fep_delete_by_1' , ut.user_login = 'myname') , p.post_date >= '2017-07-07 00:00:00'   , p.post_date <= '2017-08-12 23:59:59' order p.post_date )  union  select b.* ( select p.id, p.post_date, p.post_title, uf.user_login from_login, ut.user_login to_login  wp_posts p     join wp_postmeta pm_to     on (p.post_parent = pm_to.post_id      , p.post_parent <> 0      , pm_to.meta_key = '_fep_participants'      , pm_to.meta_value <> p.post_author)      join wp_postmeta pm_delete     on (p.id = pm_delete.post_id) left join wp_users uf on uf.id = p.post_author  left join wp_users ut on ut.id = pm_to.meta_value     (p.post_type = 'fep_message' , pm_delete.meta_value <> '_fep_delete_by_1' , uf.user_login = 'myname' or p.post_type = 'fep_message' , pm_delete.meta_value <> '_fep_delete_by_1' , ut.user_login = 'myname') , p.post_date >= '2017-07-07 00:00:00'   , p.post_date <= '2017-08-12 23:59:59' order p.post_date ) b

the result produces is:

id     |post_date              |post_title     |from_login     |to_login     1692    2017-07-07 11:45:03     c               myname          theirname    1698    2017-07-08 16:28:18     b               myname          theirname    2258    2017-08-11 23:15:10                   theirname       myname   2262    2017-08-12 16:48:05     re:a            myname          theirname

thank solaflare. did it. simplification:

select p.id, p.post_date, p.post_title, uf.user_login from_login, ut.user_login to_login  wp_posts p     join wp_postmeta pm_to     on  ((p.id = pm_to.post_id      , p.post_parent = 0) or (p.post_parent = pm_to.post_id      , p.post_parent <> 0))      , pm_to.meta_key = '_fep_participants'      , pm_to.meta_value <> p.post_author)     join wp_postmeta pm_delete     on (p.id = pm_delete.post_id) left join wp_users uf on uf.id = p.post_author  left join wp_users ut on ut.id = pm_to.meta_value     (p.post_type = 'fep_message' , pm_delete.meta_value <> '_fep_delete_by_1' , uf.user_login = 'myname' or p.post_type = 'fep_message' , pm_delete.meta_value <> '_fep_delete_by_1' , ut.user_login = 'myname') , p.post_date >= '2017-07-07 00:00:00'   , p.post_date <= '2017-08-12 23:59:59' order p.post_date

Comments

Post a Comment

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

i running os ububtu 16.04. ultimate goal able run php script cron job every minute. here php script located @ /var/www/html/tests/test/index.php : <?php $directorypath = $_server['document_root']."/tests/test/data/"; echo "directorypath: $directorypath<br><br>";//check $imagefilepathsarray = glob($directorypath . "*.{png,gif}", glob_brace); echo "<br><br>imagefilepathsarray: "; print_r($imagefilepathsarray);//check ?> when open php script in browser visiting url my-ip-address/tests/test/index.php , get: directorypath: /var/www/html/tests/test/data/ imagefilepathsarray:array( [0] => /var/www/html/tests/test/data/pic.png [1] => /var/www/html/tests/test/data/pic.gif ) before writing cron job in crontab file, need check if can run anywhere using terminal . opened ubuntu terminal @ /home/ location, , executed in terminal: sudo php /var/www/html/tests/test/index.php i got following out...
Read more

php - How can i create a user dashboard -

today have questions you, want create user dashboard site, include (obviously), users data, example -email -username -and others stuff how can information, , print them in html page? if can, how can these information via php sessions? shuld use session? example, if user login, , start new session ( session_start() ), how can data print in dashboard? should use php "project"? in advice, have day , thank time!
Read more

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -

i have jquery mobile page fixed full-screen toolbar, data-tap-toggle enabled. straight under toolbar have positioned banner should slide upwards when toolbar hiding , slide downward when toolbar showing. jquery mobile toggles toolbar applying , removing ui-fixed-hidden class - sadly, can't find in documentation of toolbar widget toggle , hide or show event that. how can detect when toolbar toggled, reposition banner? .banner { position: fixed; background-color: darkseagreen; top: 46px; min-height: 48px; width: 100%; text-align: center; line-height: 48px; } <!doctype html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"> <script src="https://code.jquery.com/jquery-1....
Read more