Hot answers tagged

6

I believe this is what you're looking for: SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, GROUP_CONCAT(cp.cat_id SEPARATOR '|') AS categories FROM exp_channel_data LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id LEFT JOIN exp_category_posts ...


6

Not sure if it is best way to do this, but something like this: SELECT ct.title AS entry_title, cd.field_id_6 AS custom_field_6, (SELECT ca.cat_name FROM exp_category_posts cp INNER JOIN exp_categories ca ON cp.cat_id = ca.cat_id WHERE entry_id = cd.entry_id AND ca.group_id = 1 LIMIT 1) AS cat_1, (SELECT ca.cat_name FROM exp_category_posts cp INNER ...


4

If you turn on the Output Profiler, you will be able to see the list of SQL queries generated for any page.


4

try to add sticky DESC {exp:query sql="SELECT entry_id AS rel_entry_id, title AS rel_title, (SELECT rel_child_id FROM exp_relationships WHERE rel_id = '{select-location}' LIMIT 1) AS rel_child_id FROM exp_channel_titles WHERE channel_id IN (5, 7) AND status='Open' ORDER BY sticky DESC, title ASC "}


3

Take a look at the example on the Field display : ex 1: SELECT action_id as option_name, method as option_label, class as optgroup FROM exp_actions ORDER BY class You will see that the query needs to mark the fields option_label and option_value within the query, using the AS operator. Ergo, to replicate your query I have assumed you wish to display ...


3

I ran it and I'm getting results just fine. Are you outputting the results correctly? $query = ee()->db->query('SELECT cat_url_title, cat_name, ch.channel_name FROM exp_categories c LEFT JOIN exp_channels ch ON ch.cat_group = c.group_id WHERE c.cat_name LIKE "%'.ee()->db->escape_str($keywords).'%"'); foreach($query->...


3

You may want to check if mod_security is installed on your server and either disable it or change the configuration. My guess is that your issues is that mod_security is seeing the SQL query in your query tag and blocking the submission.


3

Its not clear from your question whether you're wanting to show the child categories based on the parent category page you're currently viewing. We needed to do the same thing, so I wrote a quick plugin, as I don't think there's any combination of EE tags that will allow you to achieve this. The scenario we used this in was if I was on domain.com/page/...


3

You can try below query to display entries of last 45 Days. SELECT DISTINCT field_id_19 AS HomeTitle FROM exp_channel_data t1 INNER JOIN exp_channel_titles t2 ON t1.entry_id = t2.entry_id WHERE t1.field_id_19 <> "" AND ((UNIX_TIMESTAMP() - t2.entry_date) < 3888000)


2

Could you not use native parameters for this? {exp:channel:entries channel="mychannel" show_expired="only" start_on="-12 months"}


2

I believe, your options are: to change the format of date you're using on the segment_2, which isn't a good idea because EllisLab can some day finally normalize all date fields; to reformat the date to match the way its used on edit_date database column; To change the date format, I suggest you the Low Nice Date plugin: {exp:low_nice_date date="{segment_2}...


2

I'm not entirely familiar with Channel Ratings, but you should be able to do what you're looking for with the query. Try this: {exp:query sql="SELECT exp_channel_ratings.*, exp_members.screen_name as like_screen_name FROM exp_channel_ratings_likes, exp_members WHERE entry_id = '{entry_id}' and exp_channel_ratings.like_author_id = exp_members....


2

If I understand you needs correctly, and you need art list w/o grouping by artists (for list for last artworks as example) : {exp:query sql='SELECT GROUP_CONCAT(child_id SEPARATOR ',') as art_ids FROM exp_relationships LEFT JOIN exp_channel_titles ON exp_relationships.parent_id=exp_channel_titles.entry_id WHERE exp_channel_titles.status = "open" AND ...


2

You should be able to simply add: SUM(t.view_count_one) as view_count ... to your SELECT statement.


2

Yes, obviously it is possible to use the model to create a new entry. To create a new entry using model you should follow below syntax. $entry = ee('Model')->make('ChannelEntry'); $entry->Channel = $channel; $entry->site_id = ee()->config->item('site_id'); $entry->author_id = ee()->session->userdata('member_id'); $entry->...


2

https://docs.expressionengine.com/latest/fieldtypes/relationships.html#accessing-children To access the child entry to your Servers channel, just loop over the field like so: {exp:channel:entries channel="servers"} {server_application} Title : {server_application:title} <br/> Short Name : {server_application:system_short_name} ...


1

It would seem you want to do this with a php approach over using the exp:query because of some php variable you want to use with it? If so, check out the Database Class Docs over in the EE documentation. Since we know that only one member will have an id of one, we can use a simple query like so: $results = ee()->db->order_by('member_id') ->limit(...


1

The issue was that the Hosting Provider had enabled mod_security in the middle of the day (right when I had taken a slight break) without notifying me of this ahead of time. They disabled mod_security late last night and all is working correctly now. BTW, the error_log file on the hosting site had all sorts of errors notifying that the issue was ...


1

I preface the answer by saying that I do not recommend using SQL to get at your entry data this way, as it's what the Channel Entries tag and field variables are designed to do for you. That said, aggregation functions aren't currently available so I understand the desire. Grid data is stored in a separate table for each Grid field. The data you see in the ...


1

I think I've heard of this issue before, where the host forces query strings. http://ee2docs.com/urls/remove_index.php.html#exceptions Look under that reference and scroll down to the area referencing query string forcing. You apparently need to change this rule in your EE installation root's .htaccess file to match this: RewriteRule ^(.*)$ /index.php?/$1 ...


1

After reading up a bit on mySQL, I've found an answer, which seems to work. If anyone sees that this is horribly inefficient feel free to disagree! {exp:query sql="SELECT exp_members.screen_name, COUNT(exp_channel_titles.entry_id) AS number_of_posts FROM exp_channel_titles LEFT JOIN exp_members ON exp_channel_titles.author_id=exp_members.member_id ...


1

You're getting the total of all related articles because you're not specifying that you need to group the results by entry. Try: SELECT t.entry_id, t.title, COUNT(r.child_entry_id) AS entry_count FROM exp_channel_titles AS t INNER JOIN exp_playa_relationships AS r ON t.entry_id = r.child_entry_id WHERE r.parent_field_id =...


1

Unless I'm missing something this is really straightforward. The channel:entries tag assumes the open status so you don't even need to specify it. Assuming you have a relationship field in the Artists channel where you specify which pieces from the Artwork channel belong to that artist the cod is roughly: {exp:channel:entries channel="artists"} {...


1

Enable the Profiler (Admin > System Administration > Output and Debugging) so you can see what the query actually being executed is. If you aren't getting results from the second query, I would suspect some server security module is likely interfering with the request, which would be revealed somewhat in the query log, as the query you ran either won't be ...


1

Here's the query to get the site name and site label for each channel: SELECT site_name, site_label, c.site_id, channel_name, channel_id FROM exp_channels c LEFT JOIN exp_sites s ON s.site_id = c.site_id


1

If the site_id is 1: SELECT channel_id, channel_name FROM exp_channels WHERE site_id = 1; Getting the channel_id and ht e channel_name.


1

Apologies, I have just seen the related_categories_mode="yes" parameter. Problem solved.


1

Adding the specific table to the SELECT ensures it works: SELECT ct.* FROM exp_channel_titles ct There is no other title being output from the other tables, but I can see exp_channel_images does have a title field. I guess EE has issues with the ambiguity even though MySQL outputted them OK.


1

What you have will work fine. You can query any table in the database with the query module. You may, though, have issues with table prefixing. I'm not sure if the query module auto-prefixes tables or not, but in the past I've had issues with tables not named with exp_, as EE tends to automatically add the prefix to queries.


1

Date math is a pain. Here's query that should give you entries in the future: SELECT entry_id, title FROM exp_channel_titles WHERE UNIX_TIMESTAMP() < entry_date; Double check this to make sure you are getting the 200 results you are expecting. Server Time zone, User time zone, DST settings, server time etc can all throw you off, but in any case it ...


Only top voted, non community-wiki answers of a minimum length are eligible