mysql - Subquery with Wordpress posts and post_meta -

-

i know not right, although working.

i need find posts (as revenue) near given radius , location, need subquery latitude , longitude info in post_meta.

it's gonna hurt eyes, here is:

/* lat -23.6480147 lng -46.704116399999975 radius 20km 6371 para km e 3959 para miles */ select  vision_outlet.wp_posts.id 'id',      vision_outlet.wp_posts.post_title 'nome',      (         select  vision_outlet.wp_postmeta.meta_value             vision_outlet.wp_postmeta           vision_outlet.wp_postmeta.meta_key = 'formatted_address'          , vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.id     ) 'formatted_address' ,     (         select  vision_outlet.wp_postmeta.meta_value             vision_outlet.wp_postmeta           vision_outlet.wp_postmeta.meta_key = 'lat'          , vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.id     ) 'lat',     (         select  vision_outlet.wp_postmeta.meta_value             vision_outlet.wp_postmeta           vision_outlet.wp_postmeta.meta_key = 'lng'          , vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.id     ) 'lng',     (     6371 * acos( cos( radians(-23.6480147) ) * cos( radians         (              (                 select  vision_outlet.wp_postmeta.meta_value                     vision_outlet.wp_postmeta                   vision_outlet.wp_postmeta.meta_key = 'lat'                  , vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.id             )         ) ) * cos( radians         (               (                 select  vision_outlet.wp_postmeta.meta_value                     vision_outlet.wp_postmeta                   vision_outlet.wp_postmeta.meta_key = 'lng'                  , vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.id             )         ) - radians(-46.704116399999975) ) + sin( radians(-23.6480147) ) * sin( radians         (              (                 select  vision_outlet.wp_postmeta.meta_value                     vision_outlet.wp_postmeta                   vision_outlet.wp_postmeta.meta_key = 'lat'                  , vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.id             )         ) ) )         ) 'distance'  vision_outlet.wp_posts, vision_outlet.wp_postmeta  vision_outlet.wp_posts.id = vision_outlet.wp_postmeta.post_id , vision_outlet.wp_posts.post_status = 'publish' , vision_outlet.wp_postmeta.meta_key = 'formatted_address' having distance < 20 order distance

the question is: should need consider creating new table or there way refactor this?

now it's better:

/* lat -23.6480147 lng -46.704116399999975 radius 20km 6371 para km e 3959 para miles */ select  wp_posts.id,          wp_posts.post_title,          pm1.meta_value formatted_address,          pm2.meta_value lat,          pm3.meta_value lng,         6371 * acos( cos( radians(-23.6480147) ) * cos( radians( pm2.meta_value ) ) * cos( radians ( pm3.meta_value ) - radians(-46.704116399999975) ) + sin( radians(-23.6480147) ) * sin( radians ( pm2.meta_value ) ) )  'distance'  wp_posts left join wp_postmeta pm1 on (wp_posts.id = pm1.post_id , pm1.meta_key='formatted_address') left join wp_postmeta pm2 on (wp_posts.id = pm2.post_id , pm2.meta_key='lat') left join wp_postmeta pm3 on (wp_posts.id = pm3.post_id , pm3.meta_key='lng')  wp_posts.post_type = 'outlet' , wp_posts.post_status = 'publish' having distance < 20 order distance

Comments

Post a Comment

Popular posts from this blog

c++ - Creating new partition disk winapi -

-
i'am trying create new partition , mount volume new, thought createfile let me this, code : lpctstr lpfilename=l"\\\\.\\device\\harddisk0\\partition3"; handle handl=createfile( lpfilename, generic_read | generic_write, file_share_read | file_share_write, null, create_always, file_attribute_normal, null ); if (handl==invalid_handle_value) { qdebug()<<"handl invalid"<<" error"<<getlasterror();} bool success = definedosdevice(ddd_raw_target_path,l"i:",l"\\device\\harddisk0\\partition3"); if(!success) qdebug()<<" definedosdevice failed "<<getlasterror(); bflag = getvolumenameforvolumemountpoint( l"i:\\", // input volume mount point or directory /** u in dir
Read more

Android Prevent Bluetooth Pairing Dialog -

-
i'm developing internal application uses bluetooth printing. want bluetooth pairing occur without user input. have managed working trapping android.bluetooth.device.action.pairing_request broadcast. in broadcast receiver call setpin method, , pairing works ok, bluetoothpairingdialog displayed second or two, disappears - see link below. https://github.com/android/platform_packages_apps_settings/blob/master/src/com/android/settings/bluetooth/bluetoothpairingdialog.java since broadcast non-ordered, can't call abortbroadcast() , , wondering if there other way prevent pairing dialog appearing. can hook window manager in way? i haven't been able come way without modifying sdk. if you're oem, it's easy (i'm on 4.3): in packages/apps/settings/androidmanifest.xml, comment intent filter pairing dialog: <activity android:name=".bluetooth.bluetoothpairingdialog" android:label="@string/bluetooth_pairing_request"
Read more

VBA function to include CDATA -

-
i have following function include cdata: function cdatasection() dim objdom domdocument dim objkmlrootelement ikmldomelement dim objkmlelement ikmldomelement dim cdata ikmldomcdatasection set objdom = new domdocument set objkmlrootelement = objdom.createelement("balloonstyle") objdom.appendchild objkmlrootelement set objkmlelement = objdom.createelement("text") objkmlrootelement.appendchild objkmlelement set cdata = objdom.createcdatasection("text") cdata.data = "<![cdata[<b>latitude = $[latitude]</b>?]]>;" end function when run above, getting error "user defined data type not found" function. yes ripster says find library reference. vba window tools>references for list of relevant references see... http://msdn.microsoft.com/en-us/library/windows/desktop/ms763701%28v=vs.85%29.aspx however, if have hard time tracking down reference, replace dim sta
Read more